User Guide

276 Chapter 2: ColdFusion Tags
Example
The following example shows how to invoke an Oracle 8 PL/SQL stored procedure. It makes use
of Oracle 8 support of the Reference Cursor type.
The following package,
Foo_Data, houses a procedure refcurproc that declares output
parameters as Reference Cursor:
• Parameter pParam1 returns the rows in the EMP table
• Parameter pParam2 returns the rows in the DEPT table
The procedure declares one input parameter as an integer, and one output parameter as a two-
byte char varying type. Before the
cfstoredproc tag can call this procedure, it must be created,
compiled, and bound in the RDBMS environment.
CREATE OR REPLACE PACKAGE Foo_Data AS
TYPE EmpTyp IS REF CURSOR RETURN Emp%ROWTYPE;
TYPE DeptTyp IS REF CURSOR RETURN Dept%ROWTYPE;
PROCEDURE refcurproc(pParam1 in out EmpTyp, pParam2 in out DeptTyp,
pParam3 in integer, pParam4 out varchar2);
END foo_data;
CREATE OR REPLACE PACKAGE BODY Foo_Data AS
PROCEDURE RefCurProc(pParam1 in out EmpTyp,
pParam2 in out DeptTyp,
pParam3 in integer,
pParam4 out varchar2) IS
BEGIN
OPEN pParam1 FOR select * from emp;
OPEN pParam2 FOR select * from dept;
IF pParam3 = 1
THEN
pParam4 : = 'hello';
ELSE
pParam4 : = 'goodbye';
END IF;
END RefCurProc;
END Foo_Data;
The following CFML example shows how to invoke the RefCurProc procedure using
cfstoredproc, cfprocparam, and cfprocresult:
<cfstoredproc procedure = "foo_data.refcurproc"
dataSource = "oracle8i"
username = "scott"
password = "tiger"
returnCode = "No">
<cfprocparam type = "Out" CFSQLType = "CF_SQL_REFCURSOR"
variable = "param1">
<cfprocparam type = "Out" CFSQLType = "CF_SQL_REFCURSOR"
variable = "param2">
<cfprocparam type = "IN" CFSQLType = "CF_SQL_INTEGER" value = "1">
<cfprocparam type = "OUT" CFSQLType = "CF_SQL_VARCHAR"
variable = "FOO">
<cfprocresult name = "rs1">
<cfprocresult name = "rs2" resultSet = "2">
</cfstoredproc>
<b>The first result set:</b><br>