Adrian Lynch wrote:
> I don't really know much about Oracle, but from what you have posted
> in your
> code you seem to have an in/out param in the SP, if that's the case
> wouldn't
> you need another cfprocparam with type of inout? Just guessing mind.
>
>
> Ade
>
> -----Original Message-----
> From: Troy Simpson [mailto:[EMAIL PROTECTED]
> Sent: 13 October 2003 18:07
> To: CF-Talk
> Subject: Calling Oracle9i StoreProcedure from CFMX
>
> I am calling an Oracle Stored Procedure using CFMX's <cfstoredproc> tag
> and I am getting the following error and I can not seem to figure out
> the case.
> --
> Error Executing Database Query.
> [Macromedia][Oracle JDBC Driver]Incorrect parameter bindings for stored
> procedure call. Check your bindings against the stored procedure's
> parameters.
>
> The error occurred in C:\CFusionMX\wwwroot\InsideWood\model\iawa.cfc:
> line 23
>
> 21 : <cfstoredproc procedure="IAWA_PKG.getIAWAPage"
> datasource="InsideWood" debug="yes" returncode="yes">
> 22 : <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="A"
> null="no" />
> 23 : <cfprocresult name="p_rs" />
> 24 : </cfstoredproc>
> 25 :
> --
>
> I have tested the procedure outside of ColdFusion and it work
> correctly. What am I missing here? According to all the documetation
> and other List Messages, it appears to me that I am doing this right.
>
> ------------------------------
> Here is the ColdFusion Code:
> <cfstoredproc procedure="IAWA_PKG.getIAWAPage"
> datasource="InsideWood" debug="yes" returncode="yes">
> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="A"
> null="no" />
> <cfprocresult name="p_rs" />
> </cfstoredproc>
>
> <cfoutput>#now()#</cfoutput>
> <cfdump var="qry1" />
>
> -------------------------------------
> Here is the Strore Procedure definition:
> (Specification)
>
> CREATE OR REPLACE PACKAGE Iawa_Pkg AS
>
> /* Programmer-Defined Records. */
> -- Record Type for IAWA Page Records.
> TYPE IAWAPage_RecTyp IS RECORD
> (
> LEVEL NUMBER,
> pid IAWA.pid%TYPE,
> ID IAWA.ID%TYPE,
> page IAWA.page%TYPE,
> code IAWA.code%TYPE,
> description IAWA.description%TYPE,
> sortorder IAWA.sortorder%TYPE
> );
>
> -- Strong Ref Cursor based on Programmer-Defined Record.
> TYPE IAWAPage_CurTyp IS REF CURSOR RETURN IawaPage_RecTyp;
>
> /* Weak Ref Cursor for general use. */
> TYPE Generic_CurTyp IS REF CURSOR;
>
> PROCEDURE getIAWAPage
> (
> p_page IN VARCHAR2 DEFAULT 'A',
> p_rs IN OUT IAWAPage_CurTyp
> );
>
> PROCEDURE TEST;
>
> END;
> /
>
> (Body):
>
> CREATE OR REPLACE PACKAGE BODY Iawa_Pkg AS
> PROCEDURE getIAWAPage
> (
> p_page IN VARCHAR2,
> p_rs IN OUT IAWAPage_CurTyp
> )
> IS
> /* Declaration section. */
> BEGIN
> -- Open Cursor. --
> OPEN p_rs FOR
> SELECT
> LEVEL,
> pid,
> ID,
> page,
> code,
> description,
> sortorder
> FROM
> IAWA
> WHERE
> page = p_page
> START WITH ID = 0
> CONNECT BY PRIOR ID = pid
> ORDER SIBLINGS BY sortorder;
>
> END getIAWAPage;
>
> /********************************************/
> /* Test Procedure. */
> /********************************************/
> PROCEDURE TEST
> IS
> -- Declaration Section.
> lc IAWAPage_CurTyp;
> rec IAWAPage_RecTyp;
> BEGIN
> DBMS_OUTPUT.PUT_LINE('Test');
> getIAWAPage('B', lc);
> FETCH lc INTO rec;
> WHILE lc%FOUND
> LOOP
> DBMS_OUTPUT.PUT_LINE(rec.ID || ' ' || rec.Page);
> FETCH lc INTO rec;
> END LOOP;
> CLOSE lc;
> END;
>
> END;
> /
>
> --
> Troy Simpson
> Applications Analyst/Programmer, OCPDBA, MCSE, SCSA
> North Carolina State University Libraries
> Campus Box 7111 | Raleigh | North Carolina
> ph.919.515.3855 | fax.919.513.3330
> E-mail: [EMAIL PROTECTED]
>
> _____
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
