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]
