Runar, Here's a message on this from Allaire's Damon Cooper. -David --------- Forwarded message ---------- From: Damon Cooper <[EMAIL PROTECTED]> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Date: Thu, 3 Feb 2000 16:49:07 -0500 Subject: CF 4.5 Supports Oracle Reference Cursors Message-ID: <[EMAIL PROTECTED]> Just in case the guys looking for Oracle stored proc result set support haven't seen this in the 4.5 docs: ( From http://127.0.0.1/CFDOCS/Allaire_Support/new.htm#curse ) 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> On Mon, 11 Dec 2000 20:11:18 -0800 "Runar Petursson" <[EMAIL PROTECTED]> writes: > Can Anyone give me guidance on how to return record sets (ideally > multiple) > from PL/SQL using CFSTOREDPROC. > > I've got no problem using CFSTOREDPROC, it's the PL/SQL syntax I > haven't > found documentation on. > > It's real simple in SQL Server, but I haven't been able to do it in > Oracle. > > Any help is appreciated. > > Thanks > > Runar ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
