tracker issue : CF-4100344

select a category, or use search below
(searches all categories and all time range)
Title:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/UserError

Reporter/Name(from Bugbase): Stuart Pham / Stuart Pham (Stuart Pham)

Created: 12/15/2015

Components: Database

Versions: 11.0

Failure Type: Crash

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Critical / Some users will encounter

Locale/System: English / Windows 7 SP1 64-bit

Vote Count: 0

Problem Description: Upgraded to CF 11 from CF 9 and the stored procedure started breaking.

Steps to Reproduce: Call a stored procedure with cfprocresult output parameter. The query outputs come back empty although there is data produced when the same stored procedure is ran on CF9. 


See sample: 

<cftry> 
<cfstoredproc procedure="Scorecard.Maintain_scorecard.qry_getScorecardDetail" datasource="#attributes.dsn#" returncode="no">
	<!--- Common Procedure Input --->
	<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#Trim(user_id)#">
	<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#Trim(security_id)#">
	<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#Trim(last_action)#">
	<!--- Proc specific input --->
	<cfprocparam type="in" cfsqltype="cf_sql_numeric" value="#qry_EmployeeSearch.SEQ_NBR#">		
	<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#toString(EFF_DATE)#">
	<!--- return data --->	
	<cfprocresult name="qry_Dates" resultset="1">
	<cfprocresult name="qry_getScores" resultset="2">
	<cfprocresult name="qry_getRanks" resultset="3">
	<cfprocresult name="o_rc" resultset="4">
	<cfprocresult name="o_msic" resultset="5">
	<cfprocresult name="o_grade" resultset="6">
	<cfprocresult name="o_payplan" resultset="7">
	<cfprocparam type = "OUT" CFSQLType="numeric" variable="variables.audit_string">
	<cfprocparam type = "OUT" CFSQLType="numeric" variable="variables.returnCode">		
	<cfprocresult name="qry_action_data" resultset="8">
	<cfprocresult name="qry_error_data" resultset="9">	
</cfstoredproc>

<cfcatch type="any">
<cfoutput>	
	catch: #cfcatch.Message#<br />
    :: #cfcatch.Detail#<br /> 
    <hr /> 
    qry_Dates:  <cfdump var="#qry_Dates#">
    <br />
	qry_getScores: <cfdump var="#qry_getScores#">
    <br />
	qry_getRanks: <cfdump var="#qry_getRanks#">
    <br /> 
</cfoutput>
</cfcatch>
</cftry>

Actual Result:
Type: Database 
Message: Error Executing Database Query. 
Detail: [Macromedia][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1  


Expected Result:

Any Workarounds:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4100344

External Customer Info:
External Company:  
External Customer Name: Stuart Pham
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

Stuart, Please provide the information mentioned below: 1. ColdFusion' s settings summary 2. Complete Exception stacktrace
Comment by Nimit S.
5081 | December 20, 2015 01:18:19 AM GMT
Please close this ticket. This problem is not related to the CF9 to CF11 upgrade. It was a datatype issue. Specifically relating to this piece of code: <cfprocparam type = "OUT" CFSQLType="numeric" variable="variables.audit_string"> <cfprocparam type = "OUT" CFSQLType="numeric" variable="variables.returnCode"> The data type was incorrect. Instead of 'numeric', it was actually 'cf_sql_numeric' and the 'variables.returnCode' should have been 'cf_sql_varchar'. I apologize for any confusion. Yours Truly, Stuart Pham
Comment by External U.
5082 | December 21, 2015 08:07:21 AM GMT
I had it backwards. variables.audit_string should have been CFSQLType="cf_sql_varchar" and variables.returnCode should have been CFSQLType="cf_sql_numeric"
Comment by External U.
5083 | December 21, 2015 08:09:44 AM GMT
I am glad to know that your issue is resolved. I am closing this bug.
Comment by Nimit S.
5084 | December 21, 2015 08:14:36 AM GMT
One last note. It probably does have to do with the CF11 upgrade. I'm sure the tighter security and the stronger datatype verification caused the 'numeric' value to be rejected in CF11, where it was not in CF9.
Comment by External U.
5085 | December 21, 2015 08:33:10 AM GMT