CF MX7 <cfstoredproc> with Oracle

[kekico]

O.k. I am getting the following error using CF MX7 when I create a new record,
but update works fine. However, this works fine on CF MX6.1

ERROR:
Error executing database query
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Driver for
Oracle][Oracle]ora-06502; pl/sql: numeric or value error: character string
buffer too small ora-06512
CODE:
<cfstoredproc>
procedure = "P_CUST_IU_MAIN"
datasource = "powerlink"
debug = "yes"
returncode = "No">
<cfprocparam type="inout" CFSQLType=numeric value=""
variable=customer_id>
<cfprocparam type="in" CFSQLType=varchar
value="#student_name#">
<cfprocparam type="in" CFSQLType=varchar
value="#cookie.stu_id#">
<cfprocparam type="in" CFSQLType=varchar value="">
<cfprocparam type="in" CFSQLType=numeric value="1">
<cfprocparam type="out" CFSQLType=numeric
variable="O_ERROR_CODE">
<cfprocparam type="out" CFSQLType=varchar
variable="O_ERROR_MSG">
</cfstoredproc>



PLEASE HELP!

[CF_Oracle]

Please provide code of stored procedure.

[kekico]

<cfstoredproc>
procedure = "P_CUST_IU_MAIN"
datasource = "powerlink"
debug = "yes"
returncode = "No">
<cfprocparam type="inout" CFSQLType=numeric value="" variable=customer_id>
<cfprocparam type="in" CFSQLType=varchar value="#student_name#">
<cfprocparam type="in" CFSQLType=varchar value="#cookie.stu_id#">
<cfprocparam type="in" CFSQLType=varchar value="">
<cfprocparam type="in" CFSQLType=numeric value="1">
<cfprocparam type="out" CFSQLType=numeric variable="O_ERROR_CODE">
<cfprocparam type="out" CFSQLType=varchar variable="O_ERROR_MSG">
</cfstoredproc>

[paross1]

He is asking you to provide the PL/SQL of the stored procedure, not a repeat of
the CF code. At the very least, the package spec would be helpful to see the
data types of your parameters, etc. (if your procedure is in a package).

Phil

[kekico]

Sorry about that.....
it is an API that is being called, don't have the code, but here is the syntax
requested by the vendor:

P_CUST_IU_MAIN(<IO_CUID>, <I_NAME>, <I_SSN>, <I_DL_NUM>, <I_CUST_GRP_ID>,
O_ERROR_CODE, O_ERROR_MSG);

Also, it works fine in CF MX 6.1, just does not like it in CF MX 7!
Thanks for any help!

[kekico]

:) Finally, we have a solution to others that may be interested.
For whatever reason, if you set the value to the length of the value being
returned, or greater, in 0's, it works!
Another words, we changed our code to look like this, and boom no error! (the
length of our return value is 5 in this case)
<cfprocparam type="inout" CFSQLType=numeric value="00000" variable=customer_id>

[plum1120]

This workaround is fine I suppose if you have a finite return set, but we have
this same problem, and our returns are of indefinite size - in our case, rows
upon rows of data. We have tried switching to jdbc drivers with no luck, and
moving back to the 1.4 JVM vice 1.5 made no difference as well. We're on 7.0.1
using JRE 1.5.0_01, Oracle 9i driver 9.02.00.00.

This issue seems similar to an issue "fixed" in the 6.1 updater. Perhaps it
has resurfaced? I can provide further detail if anyone is interested in
getting to the bottom of this.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC][Ora]ORA-06502:
PL/SQL: numeric or value error: character string buffer too small ORA-06512: at
"DBA.PKG_DATA", line 34 ORA-06512: at line 1

<cfprocparam type = "IN" CFSQLType = "CF_SQL_VARCHAR" value =
"#session.my_oid#">
<cfprocparam type="Out" cfsqltype="CF_SQL_CHAR" variable="mbt"
dbvarname="o_mbt">
<cfprocresult name = "QRY_rc_rcm_component_available" >
</cfstoredproc>

[paross1]

You are returning "rows and rows of data"? Why aren't you using a reference cursor to return your result set?

Phil

[paross1]

Interesting. This doesn't look like a ColdFusion issue, but a problem with your
PL/SQL stored procedure. Per Oracle Metalink:

Your PL/SQL script is returning the Oracle error 'ORA-06502:
PL/SQL: numeric or value error'.

Problem Explanation:
====================

You may be trying to do one of the following:
1. SELECT or FETCH a value that is too large into a variable
2. assign a value to a variable that is longer than the declared size of a
variable
3. concatenate two long variales into a third long variable.
4. convert a character string to a number


Solution Description:
====================

In your PL/SQL script, you are trying to concatenate 2 long variables into
a third long variable and you are getting the Oracle error 'ORA-06502:
PL/SQL: numeric or value error'. The result of concatenating the two
long variables is greater than 32760 bytes.

Solution Explanation:
=====================

In PL/SQL, variables of type LONG, CHAR, VARCHAR2, and VARCHAR are
limited to 32K. You can use the Oracle Precompilers or the Oracle Call
Interface (OCI) to do this instead. See the Programmer's Guide to the
Oracle Precompilers and the Programmer's Guide to the Oracle Call
Interface for more information.

This doesn't look like a problem with your result set returned by your
reference cursor, but perhaps a problem internal to your procedure.

Phil

[plum1120]

I'll bring this to my DBA, and I'm inclined to hope you're right, except that
this runs flawlessly under our CFMX 6.1 environment, using the same 9i client
install. That's why I'm pinning this to some kind of 7.0.1 bug.

[paross1]

How large do o_mbt and session.my_oid get?

Phil

[jsantelli]

How was this fixed for the reference cursor?