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, IDIAWA.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]
RE: Calling Oracle9i StoreProcedure from CFMX
As a guess, check you are sending the correct number of params and that they are of the correct type for Oracle. 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, IDIAWA.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]
RE: Calling Oracle9i StoreProcedure from CFMX
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, IDIAWA.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]
Re: Calling Oracle9i StoreProcedure from CFMX
What would this other CFPROCPARAM look like? 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, IDIAWA.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]
RE: Calling Oracle9i StoreProcedure from CFMX
cfstoredproc procedure=IAWA_PKG.getIAWAPage datasource=InsideWood debug=yes returncode=yes cfprocparam type=In cfsqltype=CF_SQL_VARCHAR value=A null=no / cfprocparam type=InOut cfsqltype=CF_SQL_?? value=Something variable=SomethingElse / /cfstoredproc But then I've noticed the in/out param in the SQL is a custom type. Not sure what to do with this :OS Ade -Original Message- From: Troy Simpson [mailto:[EMAIL PROTECTED] Sent: 13 October 2003 18:55 To: CF-Talk Subject: Re: Calling Oracle9i StoreProcedure from CFMX What would this other CFPROCPARAM look like? 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, IDIAWA.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]
Re: Calling Oracle9i StoreProcedure from CFMX
I do not believe this method is support in MX 6.1 as in previous version. But here is what I tried: --- cfstoredproc procedure=IAWA_PKG.getIAWAPage datasource=InsideWood debug=yes returncode=yes cfprocparam type=In cfsqltype=CF_SQL_VARCHAR value=A null=no cfprocparam type=InOut cfsqltype=CF_SQL_REFCURSOR value=xxx cfprocresult name=p_rs resultset=1 /cfstoredproc Here is the Error I got back: Error Executing Database Query. [Macromedia][Oracle JDBC Driver]The specified SQL type is not supported by this driver. The error occurred in C:\CFusionMX\wwwroot\InsideWood\model\iawa.cfc: line 25 23 : cfprocparam type=In cfsqltype=CF_SQL_VARCHAR value=A null=no 24 : cfprocparam type=InOut cfsqltype=CF_SQL_REFCURSOR value=xxx 25 : cfprocresult name=p_rs resultset=1 26 :/cfstoredproc 27 : [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Calling Oracle9i StoreProcedure from CFMX
I just can not seem to figure out what I am missing because I am doing it exactly like this article on Macromedia's Web site: http://www.macromedia.com/devnet/mx/coldfusion/articles/stored_procs.html I wonder if this is a BUG that was introduced in CFMX 6.1. Adrian Lynch wrote: cfstoredproc procedure=IAWA_PKG.getIAWAPage datasource=InsideWood debug=yes returncode=yes cfprocparam type=In cfsqltype=CF_SQL_VARCHAR value=A null=no / cfprocparam type=InOut cfsqltype=CF_SQL_?? value=Something variable=SomethingElse / /cfstoredproc But then I've noticed the in/out param in the SQL is a custom type. Not sure what to do with this :OS Ade -Original Message- From: Troy Simpson [mailto:[EMAIL PROTECTED] Sent: 13 October 2003 18:55 To: CF-Talk Subject: Re: Calling Oracle9i StoreProcedure from CFMX What would this other CFPROCPARAM look like? 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, IDIAWA.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]
Re: Calling Oracle9i StoreProcedure from CFMX
ALL, I believe I have FOUND the problem. I removed the returncode attribute from the CFSTOREDPROC tag and that seemed to fix the problem. I guess this attribute is not supported in the ORACLE JDBC Driver. Here is the Original Line: cfstoredproc procedure=IAWA_PKG.getIAWAPage datasource=InsideWood debug=yes returncode=yes Here is the New Line: cfstoredproc procedure=IAWA_PKG.getIAWAPage datasource=InsideWood debug=yes Thanks, Troy Adrian Lynch wrote: cfstoredproc procedure=IAWA_PKG.getIAWAPage datasource=InsideWood debug=yes returncode=yes cfprocparam type=In cfsqltype=CF_SQL_VARCHAR value=A null=no / cfprocparam type=InOut cfsqltype=CF_SQL_?? value=Something variable=SomethingElse / /cfstoredproc But then I've noticed the in/out param in the SQL is a custom type. Not sure what to do with this :OS Ade -Original Message- From: Troy Simpson [mailto:[EMAIL PROTECTED] Sent: 13 October 2003 18:55 To: CF-Talk Subject: Re: Calling Oracle9i StoreProcedure from CFMX What would this other CFPROCPARAM look like? 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, IDIAWA.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]