Ryan A. Geesaman
Applications Developer
Penn State College of Medicine
Health Evaluation Sciences
>I recently got this to work in CFMX against Oracle 9i. Here is my
>code...sorry its broke up, can't figure out how to get Toad to give me
>it in one chunk...not enough coffee yet!
>
>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]
