LiveDocs Comments - coldfusion - 6 - CFML_Reference - Tags-pt215.htm

coldfusion/6/CFML_Reference/Tags-pt215.htm

2007-04-27T05:14:38

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

jrunrandy — 2005-06-30T09:45:00

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.

coldfusion/6/CFML_Reference/Tags-pt215.htm

2005-06-03T02:00:24

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 :

Regards.

coldfusion/6/CFML_Reference/Tags-pt215.htm

jrunrandy — 2005-03-18T13:09:19

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

2005-03-15T11:02:02

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

PurpleShoes — 2005-01-19T07:18:18

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?

coldfusion/6/CFML_Reference/Tags-pt215.htm

jrunrandy — 2004-11-17T18:09:37

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.

coldfusion/6/CFML_Reference/Tags-pt215.htm

badugi77 — 2004-11-15T14:40:08

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?

coldfusion/6/CFML_Reference/Tags-pt215.htm

alejandro2004 — 2004-09-24T14:04:30

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

HomerJay32 — 2004-08-04T07:38:21

I cannot get the 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 tag. However, I cannot use inline queries in production applications.
Any ideas?

coldfusion/6/CFML_Reference/Tags-pt215.htm

gana78 — 2004-07-04T22:33:39

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?

coldfusion/6/CFML_Reference/Tags-pt215.htm

2004-01-13T07:55:28

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

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

CFH4X0R — 2004-01-12T03:45:41

Hy all, somebody have a problem using an instance like:

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

jrunrandy — 2003-11-07T08:18:06

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

2003-11-07T08:01:32

The "cf_sql_blob" value for the "cfsqltype" attribute for 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 is used with the equivalent values, it works. Go figure.

coldfusion/6/CFML_Reference/Tags-pt215.htm

TrinityNeo — 2003-10-28T17:23:07

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

luckydoginlr — 2003-03-28T17:32:00

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.

coldfusion/6/CFML_Reference/Tags-pt215.htm

luckydoginLR — 2003-03-27T17:11:00

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?

coldfusion/6/CFML_Reference/Tags-pt215.htm

luckydoginLR — 2003-03-27T17:00:00

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?

coldfusion/6/CFML_Reference/Tags-pt215.htm

rnielsen — 2002-12-05T13:06:00

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.

coldfusion/6/CFML_Reference/Tags-pt215.htm

pfreitag — 2002-08-18T16:59:00

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

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

ctina — 2002-07-25T20:47:00

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

xrubin — 2002-07-05T19:43:00

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

kjabbour@macromedia. — 2002-07-02T18:51:00

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

coldfusion/6/CFML_Reference/Tags-pt215.htm

kjabbour@macromedia. — 2002-07-02T18:51:00

coldfusion/6/CFML_Reference/Tags-pt215.htm

paross1 — 2002-07-02T17:36:00

Your own documentation for tag 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

coldfusion/6/CFML_Reference/Tags-pt215.htm

fusebox_steve — 2002-07-01T22:25:00

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.

coldfusion/6/CFML_Reference/Tags-pt215.htm

rbils@amkor.com — 2002-06-28T13:48:00

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?

coldfusion/6/CFML_Reference/Tags-pt215.htm

crisostomo — 2002-06-10T12:54:00

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.