MACROMEDIA COLFUSION MX 7-CFML, Reference

Page: 339 / 1108

cfprocparam 339
Output variables are stored in the ColdFusion variable specified by the
variable
attribute.
You cannot use the
cfprocparam
tag for Oracle 8 and 9 reference cursors. Instead, use the
cfprocresult
tag.
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"