package def:
CREATE OR REPLACE PACKAGE PKG_DOUG
AS
TYPE myCursor IS REF CURSOR;
PROCEDURE mySP(inPosnbr IN VARCHAR2, rsCursor OUT myCursor);
END PKG_DOUG;
/
package body:
CREATE OR REPLACE PACKAGE BODY PKG_DOUG
AS
PROCEDURE mySP(inPosnbr IN VARCHAR2, rsCursor OUT myCursor)
IS
BEGIN
OPEN rsCursor FOR
SELECT
emplid, posnbr, name
FROM
pspath
WHERE
reports_to = inPosnbr
ORDER BY
name;
END mySP;
END PKG_DOUG;
/
Now the CF code: Note, no need for the maxrows="-1" anymore
<cfstoredproc procedure="pkg_doug.mySP" datasource="mydsn">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="inPosnbr" value="#form.posnbr#">
<cfprocresult name="foo">
</cfstoredproc>
HTH
Doug
-----Original Message-----
From: Ryan Geesaman [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 8:15 AM
To: CF-Talk
Subject: Returning Results from a Stored Proc in Oracle
I am trying to use <cfstoredproc> to return results from Oracle. I have seen a few different suggestions on how to accomplish this, but none have worked.
First Try:
In Oracle --
PACKAGE STORED_PROC_TEST
IS
TYPE CUSTOM_REF_CURSOR IS REF CURSOR;
PROCEDURE get_data(p_criteria IN VARCHAR2,
p_the_results IN OUT CUSTOM_REF_CURSOR);
END;
In CF --
<cfstoredproc procedure="stored_proc_test.get_data" datasource="#session.dsn#">
<cfprocparam type="in"
cfsqltype="cf_sql_varchar"
value="test"
dbvarname="p_criteria">
<cfprocparam type="out"
cfsqltype="cf_sql_refcursor"
variable="p_the_results">
<cfprocresult name="dataFromStoredProc">
</cfstoredproc>
This results in "The specified SQL type is not supported by this driver."
Second Try:
In Oracle -- same as above
In CF -- second <cfprocparam> is dropped completely
This results in "wrong number or types of arguments in call to 'GET_DATA'"
Any help would be appreciated,
Ryan A. Geesaman
Applications Developer
Penn State College of Medicine
Health Evaluation Sciences
_____
[ Todays
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
