CFML Reference

cfprocparam

Description

Parameter information. This tag is nested within a cfstoredproc tag.

Category

Database manipulation tags

Syntax

<cfprocparam 
  type = "in" or "out" or "inout"
  variable = "variable name"
  dbVarName = "DB variable name"
  value = "parameter value"
  CFSQLType = "parameter datatype"
  maxLength = "length"
  scale = "decimal places" 
  null = "Yes" or "No"> 

See also

cfinsert, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cftransaction, cfupdate

Attributes

Attribute	Req/Opt	Default	Description
type	Optional	in	in: passes the parameter by value. out: passes parameter as bound variable inout: passes parameter as a bound variable
variable	Required if type = "OUT" or "INOUT"		ColdFusion variable name; references the value that the output parameter has after the stored procedure is called.
dbVarName	Required for named notation		Parameter name that corresponds to the name of the parameter in the stored procedure.
value	Required if type = "OUT" or "INOUT"		Value that corresponds to the value that ColdFusion passes to the stored procedure.
CFSQLType	Required		SQL type to which the parameter (any type) is bound: CF_SQL_BIGINT CF_SQL_BIT CF_SQL_BLOB CF_SQL_CHAR CF_SQL_CLOB CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARCHAR
maxLength	Optional	0	Maximum length of parameter.
scale	Optional	0	Number of decimal places in parameter.
null	Optional	No	Whether parameter is passed as a null value. Yes: tag ignores the value attribute No

Usage

Use this tag to identify stored procedure parameters and their data types. Code one cfprocparam tag for each parameter. The parameters that you code vary based on parameter type and DBMS. The order in which you code cfprocparam tags depends on whether the stored procedure uses positional or named notation:

• Positional notation: ColdFusion passes parameters to the stored procedure in the order in which they are defined
• Named notation: The dbVarName for the parameter must correspond to the variable name in the stored procedure on the server

Output variables are scoped with the name of the variable attribute passed to the tag.

CFML supports Oracle 8 Reference Cursor type, which passes a parameter by reference. Parameters that are passed this way can be allocated and deallocated from memory within the execution of one application.

To use reference cursors in packages or stored procedures, use the cfprocresult tag. This causes Datadirect JDBC to put Oracle reference cursors into a result set. (You cannot use this method with Oracle's ThinClient JDBC drivers.)

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>
<hr>
<cftable query = "rs1" colHeaders HTMLTable border = "1">
  <cfcol header = "EMPNO" text = "#EMPNO#">
  <cfcol header = "EMPLOYEE name" text = "#ENAME#">
  <cfcol header = "JOB" text = "#JOB#">
  <cfcol header = "SALARY" text = "#SAL#">
  <cfcol header = "DEPT NUMBER" text = "#DEPTNO#">
</cftable>

<hr>
<b>The second result set:</b><br>

<cftable query = "rs2" colHeaders HTMLTable border = "1">
  <cfcol header = "DEPT name" text = "#DNAME#">
  <cfcol header = "DEPT NUMBER" text = "#DEPTNO#">
</cftable>
<hr>
<cfoutput>
  <b>The output parameter is:</b>'#FOO#'
</cfoutput>

Copyright © 2002, Macromedia Inc. All rights reserved.

Comments