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]
