cfprocparam

<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">

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;

<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>

Comments

kjabbour@macromedia. said on Jul 2, 2002 at 6:51 PM :

ColdFusion MX ignoring the dbvarname attribute of cfprocparam is documented in Migrating ColdFusion 5 Applications, CFML Tags and Attributes > cfprocpram:
----
Ignores the dbvarname attribute for all drivers. (ColdFusion 5 used it for Sybase, Oracle, and Informix native drivers.) ColdFusion MX is a JDBC client which does not support named parameter passing like the Sybase ctlib and Oracle oci libraries.
----

Regards,
~Kristi Jabbour
Senior Technical Writer
Macromedia

crisostomo said on Jun 10, 2002 at 12:54 PM :

CFSQLType - does not say which ones pertain to which DB. My Ben Forta CF 4.0 Book says cf_sql_varchar, cf_sql_char, cf_sql_longvarchar, cf_sql_numeric are supported in Oracle. Are there others besides cf_sql_refcursor.

I am not able to get the refcursor method to work on our CF 5.0 Production server or my evaluation MX server (for Oracle 8i). I've noted the Knowledge base documentation as well, for actual coding examples.

Also, what happened to maxRows supported in CF 5.0 ?? The documentation could at least indicate, uder which versions, each parameter/attribute is valid.

paross1 said on Jul 2, 2002 at 5:36 PM :

Your own documentation for tag <cfprocparam> at http://livedocs.macromedia.com/cfmxdocs/Migrating_ColdFusion_5_Applications/cf_migration_guide6.jsp#1203166 contradicts the method stated here for returning result sets from Oracle stored procedures.

"If you have ref cursors in packages or stored procedures, use cfprocresult instead. This causes Datadirect JDBC to place the Oracle ref cursors into a result set. (Note: you cannot use this method with the Oracle ThinClient JDBC drivers.)"

Accordiong to the above statement, with MX you do not use <cfprocparam type="OUT" cfsqltype="cf_sql_refcursor"... to return result sets from Oracle procedures, but omit the parameter altogether and use <cfprocresult... instead. I have confirmed the new behavior by testing with CFMX and Oracle 8.0.6.

rbils@amkor.com said on Jun 28, 2002 at 1:48 PM :

In the Code Compatibility analyzer, it says that the DBVARNAME attribute for CFPROCPARAM is ignored in ColdFusion MX. However, the docs do not reflect this. Also not mentioned in the release notes, doc additions, or updates. Which is correct, the docs or the code compatibility analyzer?

fusebox_steve said on Jul 1, 2002 at 10:25 PM :

Just so you know, cfprocessingdirective, cfsilent and cfsetting have made whitespace suppression very confusing.

The documentation does not make it clear what the differences between the 3 tags are.

kjabbour@macromedia. said on Jul 2, 2002 at 6:51 PM :

ColdFusion MX ignoring the dbvarname attribute of cfprocparam is documented in Migrating ColdFusion 5 Applications, CFML Tags and Attributes > cfprocpram:
----
Ignores the dbvarname attribute for all drivers. (ColdFusion 5 used it for Sybase, Oracle, and Informix native drivers.) ColdFusion MX is a JDBC client which does not support named parameter passing like the Sybase ctlib and Oracle oci libraries.
----

Regards,
~Kristi Jabbour
Senior Technical Writer
Macromedia

xrubin said on Jul 5, 2002 at 7:43 PM :

I am migrating a CF5 application that uses Oracle stored procs and returns REF CURSORS to CFMX. I get the following error when trying to return a REF CURSOR:
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver]The specified SQL type is not supported by this driver.
SQL {call Login.EducatorLogin( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) )}
DATASOURCE xxx
SQLSTATE HY004

This worked fine in CF5 and I'm not using DBVAR. Environment is Windows 2000. Please help - our entire app is written this way

ctina said on Jul 25, 2002 at 8:47 PM :

Response from Macromedia to crisostomo:
The SQL data types depend on the database. If a database supports varchar, then you can the sql datatype param.

ref_cursors they work in CF5 and CFMX.

If you have a problem, please open a Tech Support call. On Livedocs, post only documentation issues. Thanks.
Christina Lamkin, ColdFusion team

pfreitag said on Aug 18, 2002 at 4:59 PM :

The value attribute reads: Required if type = "OUT" or "INOUT"

it should be Required if type = "IN" or "INOUT"

rnielsen said on Dec 5, 2002 at 1:06 PM :

MAcromedia update (bug 49229)
The Maxlength attribute of cfprocparam works differently than in CF5. This has not been documented.
* In CF5, maxlength was used with cfprocparam type="out" to retrieve data greater than 255 characters. Maxlength had no effect when using it with cfprocparam type="in".
* In CFMX, maxlength is no longer needed when retrieving data greater than 255 characters. It seems to be ignored when type="out". When type="in", the below error will be generated when trying to pass data greater than the maxlength attribute.

ERROR:
Invalid data xxxxxxxxxxxxxxxxxx ...
value exceeds MAXLENGTH setting 8.

luckydoginLR said on Mar 27, 2003 at 5:00 PM :

ColdFusion MX online documentation does not make it clear whether CFIF statements can be used inside a CFSTOREDPROC routine in order to choose between several CFPROCPARAM statements. My issue is that I may want my CFSTOREDPROC to contain certain of my CFPROCPARAM statements with NULL="yes" under certain curcumstances. There are no examples of this that I can find in the documentation. Another option might be to use a variable with the NULL= option, where the variable was earlier loaded with "yes" or "no". Does anyone know if a null statement could read NULL=#VAR_YESNO#, where the variable #VAR_YESNO# had been cfset with yes or no earlier in the program?

luckydoginLR said on Mar 27, 2003 at 5:11 PM :

The VALUE attribute of CFPROCPARAM seems to work exactly the opposite of the way the documentation above says it should. I find that a value clause is only required if the TYPE = "IN". It seems to be optional if the type = "OUT" or "INOUT". What's up with this?

luckydoginlr said on Mar 28, 2003 at 5:32 PM :

I will answer my own question above. I tested using a variable, instead of "yes" or "no" with a NULL= attribute in a CFPROCPARAM statement. It seems to work fine. The attribute is picked up correctly by the stored procedure, and a SQL query statement is generated accordingly. This is especially handy when you have a query input form where the users may leave one or more fields blank from time to time. You do not want your query statement trying to locate records with indexes that were blank. Faced with a possibility of having different blank fields on each submission of the form, the only realistic way to handle this is to have logic to change the nulls= value outside the CFPROCPARAM statement.

TrinityNeo said on Oct 28, 2003 at 5:23 PM :

<cfprocparam
type="In" variable="p_mydatevar" value = "#myStruct["mydatevar"]#" cfsqltype="CF_SQL_DATE">

In the above SP call, how can I handle NULL values in dates??

No screen name said on Nov 7, 2003 at 8:01 AM :

The "cf_sql_blob" value for the "cfsqltype" attribute for <cfprocparam> returns the following error (from SQL Server 2000):

Description:
Attribute validation error for tag CFPROCPARAM.The value of the attribute VALUE is invalid. The value cannot be converted to a string because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values.

Detail:
The value of the attribute VALUE is invalid. The value cannot be converted to a string because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values.

When a <cfqueryparam> is used with the equivalent values, it works. Go figure.

jrunrandy said on Nov 7, 2003 at 8:18 AM :

Have you applied updater 3? This issue (or a similar one) was fixed, according to the U3 release notes: http://www.macromedia.com/support/coldfusion/releasenotes/mx/releasenotes_mx_updater01.html

CFH4X0R said on Jan 12, 2004 at 3:45 AM :

Hy all, somebody have a problem using an instance like:
<cfprocparam type="out" cfsqltype="cf_sql_date" dbvarname="startDate" variable="CFstartDate" value="01/01/1900" null="no">

If I invoke a stored procedure with this param i only recive an error where the description only contains number 1.

Thanks a lot in advance

No screen name said on Jan 13, 2004 at 7:55 AM :

can I use CF_SQL_REFCURSOR to pass query values to a stored procedure, if not is there anything at all that can be used??

<cfargument name="my_list" type="query" required="true" hint="My List">

<cfprocparam type = "IN or OUT" CFSQLType = "CF_SQL_REFCURSOR"
variable = "#arguments.my_list#">

I can't find any documentation that could point me to the right direction.

Thank you in advance for your help

gana78 said on Jul 4, 2004 at 10:33 PM :

When the length of the variable being passed to MS SQL server is greater than 4000, CF automatically assumes that the variable is of type NTEXT and since the sql stored procedure has the param defined as VARCHAR(8000), it throws an error. Why does CF assume falsely, that everything above 4000 is TEXT and not VARCHAR even though SQL server allows upto 8000?

HomerJay32 said on Aug 4, 2004 at 7:38 AM :

I cannot get the <cfprocparam> tag to pass a blob to an oracle 9i stored procedure. The tag's type is set to cf_sql_blob. I have tried numerous types and variable manipulations and always get the same error :
"The value cannot be converted to a string because it is not a simple value...". I have the datasource set to allow blob and clob retrieval.
This works fine if I use an inline query with the <cfqueryparam> tag. However, I cannot use inline queries in production applications.
Any ideas?

alejandro2004 said on Sep 24, 2004 at 2:04 PM :

CFPROCPARAM cfsqltype="CF_SQL_REFCURSOR" error data type invalid for oracle 9

badugi77 said on Nov 15, 2004 at 2:40 PM :

How does it work with double-byte foreign lanugages? Can I pass a Korean word to the stored procedure? If so, what cf_sqltype should I use?

jrunrandy said on Nov 17, 2004 at 6:09 PM :

char, varchar, nchar and nvarchar should use CF_SQL_VARCHAR even if it's double byte data.
Also, if you are using SQL Server, be sure to check the 'String Format' option in advanced settings for the data source.

PurpleShoes said on Jan 19, 2005 at 7:18 AM :

I am migrating from CF4.51 to MX and cannont get some of my Oracle Stored procedures to work. I cannot find any reference to procedure overloading in any of the CF... tag docs related to stored procedures, nor can I find any references in the migration docs.

Can you refer me to anything regarding this? Does MX support function and procedure overloading like 4.5 did? Only the first procedure in the package works, the remaining error out with a parameter error message,, yet I can use the exact same cf tag in a procedure that is not overloaded and it works.

Is this documented somewhere?

No screen name said on Mar 15, 2005 at 11:02 AM :

PurpleShoes: We are having the same problem. Have you been able to resolve it?

jrunrandy said on Mar 18, 2005 at 1:09 PM :

You cannot use cfprocparam for Oracle ref cursors. There is a mistake in the code example on this page that implies you can.

I apologize for the confusion

No screen name said on Jun 3, 2005 at 2:00 AM :

Did any of you tried to call an Oracle overloaded stored procedure ?
You will be amazed to know that it doesn't work. :))

Please test this :
Write two Oracle stored procedures having the same name but different parameters :

CREATE OR REPLACE PACKAGE BODY Test
AS

-- one parameter --
PROCEDURE TestProc(y OUT ref_cursor)
IS
BEGIN
OPEN y FOR SELECT 1 FROM dual;
END TestProc;

-- 2 parameters
PROCEDURE TestProc(x VARCHAR2, y OUT ref_cursor)
IS
BEGIN
OPEN y FOR SELECT 2 FROM dual;
END TestProc;

END;

Don't forget to write also the specification of the package and then try to call one of these procedures from coldfusion :

<CFSTOREDPROC procedure="TEST.TESTPROC" datasource="DS" username="USER" password="PASS">
<cfprocparam type="In" cfsqltype="cf_sql_varchar" value="param" >
<cfprocresult name="rezultat">
</CFSTOREDPROC>

Regards.

jrunrandy said on Jun 30, 2005 at 9:45 AM :

I checked and regret to say that this isn't supported. There is a bug for this issue, 53122, but it hasn't been addressed as of CFMX 7.

No screen name said on Apr 27, 2007 at 5:14 AM :

jrunrandy said on Mar 18, 2005 at 1:09 PM
You cannot use cfprocparam for Oracle ref cursors. There is a mistake in the code example on this page that implies you can.
I apologize for the confusion

So, can ColdFusion MX 7 accept ref cursors from an Oracle stored procedure at all? Why am I asking this question 2 years later?
Thanks