Deanna,
Sorry, the proper code snippet should look like this:
<!--- This cfscript block just creates a simple recordset --->
<cfscript>
empRS = QueryNew("l_emp_number,l_first_name");
for(i = 1;i lte 3;i = i + 1){
QueryAddRow(empRS);
QuerySetCell(empRS,"l_emp_number",i);
QuerySetCell(empRS,"l_first_name","Tester" & i);
}
</cfscript>
<cfstoredproc procedure="flextest.test1" datasource="TARDIS">
<cfprocparam cfsqltype="cf_sql_refcursor" type="In"
dbvarname="In_Employee" value="empRS">
</cfstoredproc>
And the Oracle Stored procedure is defined something like this:
TYPE tRecordset IS REF CURSOR;
..
PROCEDURE Test1(
In_Employee IN tRecordset)
IS
l_Emp_Number varchar2(100);
l_First_Name varchar2(100);
BEGIN
loop
fetch In_Employee into l_Emp_Number, l_First_Name;
insert into flextesttable
values
(l_Emp_Number,l_First_Name);
exit when In_Employee%notfound;
end loop;
close In_Employee;
end if;
commit;
END Test1;
The above code still produces the Oracle Error:
ORA-24338: statement handle not executed
And this is caused by the "fetch..." line, where Oracle is saying you cannot
fetch from this ref cursor, until it is opened, except a ref cursor by
definition is already open.
If you know of any way that a recordset can be passed in to an Oracle Stored
Procedure, I would be will ing to give it a try.
Thanks,
Maurice.
-----Original Message-----
From: Deanna Schneider [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 24, 2001 6:39 AM
To: CF-Talk
Subject: Re: Passing a recordset INTO an Oracle Stored Procedure
Maurice,
In your code, you indicate no value is being passed in. Is that what you
meant to indicate? I would guess that Oracle would balk at an in parameter
with no value unless you have a default value set up in your stored proc.
><cfprocparam cfsqltype="cf_sql_refcursor" type="IN" dbvarname="In_Employee"
value=""> <-----Really? No value?
Deanna Schneider
Interactive Media Developer
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
