>From the CF 4.51 release notes:
Oracle Reference Cursor Support
This release supports the return of result sets via Oracle Reference Cursors
through the Oracle 8 native driver and the ColdFusion stored procedure
interface.
The example below shows both the PL/SQL necessary for creating a procedure
to return a result set and the CFML necessary for executing the procedure in
ColdFusion. The example uses the Oracle SCOTT/TIGER login sample data. The
final ColdFusion documentation for this release will contain additional
information and examples.
Example: Return of all rows in the DEPT table entirely through a package.
PL/SQL
CREATE OR REPLACE PACKAGE dept_data AS
TYPE DeptTyp IS REF CURSOR RETURN dept%ROWTYPE;
PROCEDURE refcurproc(pParam1 in out DeptTyp);
END dept_data;
CREATE OR REPLACE PACKAGE BODY dept_data AS
PROCEDURE refcurproc(pParam1 in out deptTyp) IS
BEGIN
OPEN pParam1 FOR select * from dept;
END refcurproc;
end dept_data;
CFML
<cfstoredproc procedure="dept_data.refcurproc"
datasource="my_oracle80_test"
username = scott
password = tiger
RETURNCODE="no"
>
<cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" variable="param1">
<cfprocresult name = rs1>
</cfstoredproc>
<b>The first result set:</b><br>
<hr>
<cfoutput query = rs1>
#dname#,#deptno#
</cfoutput>
-----Original Message-----
From: Clint Tredway [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 09, 2001 8:32 AM
To: CF-Talk
Subject: Oracle Stored Procedures
Can anyone help me with the syntax of executing a stored procedure in
Oracle?
I have one input parameter(grpid) and one output parameter(tpcid) and they
are both numeric.
Here is what I am trying:
<cfstoredproc
procedure="slct_topic_tpcid"
datasource="#appDSN#"
returncode="yes">
<cfprocparam type="In"
dbvarname="grpid"
value="100"
cfsqltype="CF_SQL_Numeric">
<cfprocparam type="Out"
dbvarname="tpcid"
cfvarname="newtpcid"
cfsqltype="CF_SQL_NUMERIC">
<cfprocresult name="proctest" resultset="1000">
</cfstoredproc>
With the code about, I get a syntax error..
Thanks!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
