Skip to main content
Participating Frequently
February 8, 2023
Answered

ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

  • February 8, 2023
  • 2 replies
  • 2085 views

I am getting the following error when trying to connect CF2021 and Oracle 19. Please let me know if anyone has faced similar issues before..

 

Only complexity is that the StoredProcs are created referencing Column type.

 

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

 

This code works when run form CF10 environment (Driver used SeeFusion (Oracle)

However from CF2018 or CF2021 it does not work (Driver used Oracle)

 

Below is a sample code (cfml page).I have tried with CF_SQL_NUMERIC as well instead of CF_SQL_DECIMAL

		<cfset arguments = StructNew() />
		<cfset arguments.username = "userName" />
		<cfset arguments.P_GROUP_PR_SEQ_NO = "10" />
		<cfset arguments.groupName = "g" />
		<cfset arguments.cboID = "17065" />
		<cfset arguments.P_GROUP_MANAGER_PR_SEQ_NO = "10" />
		<cfset arguments.groupIdentifier = "g" />
		<cfset arguments.groupDescription = "g" />
		
		<cfset dsn = "dsnName" />		
		<cfset Localscope = StructNew() />

		<cfdump var="#arguments#" />
		<cfdump var="#dsn#" />

		<CFSTOREDPROC procedure="testProc" datasource="#dsnName#">				
			<CFPROCPARAM dbvarname="P_NT_USER_ID" value="#arguments.userName#" cfsqltype="CF_SQL_VARCHAR" type="in">
			<CFPROCPARAM dbvarname="P_GROUP_PR_SEQ_NO" value="#arguments.P_GROUP_PR_SEQ_NO#" null="true" cfsqltype="CF_SQL_DECIMAL" type="in">
			<CFPROCPARAM dbvarname="P_GROUP_NAME" value="#arguments.groupName#" null="true" cfsqltype="CF_SQL_VARCHAR" type="in">
			<CFPROCPARAM dbvarname="P_PARENT_CBO_PR_SEQ_NO" value="#Arguments.cboID#" null="true" cfsqltype="CF_SQL_DECIMAL" type="in">
			<CFPROCPARAM dbvarname="P_GROUP_MANAGER_PR_SEQ_NO" value="#arguments.P_GROUP_MANAGER_PR_SEQ_NO#" null="true" cfsqltype="CF_SQL_DECIMAL" type="in">
			<CFPROCPARAM dbvarname="P_GROUP_ID" value="#arguments.groupIdentifier#" null="true" cfsqltype="CF_SQL_VARCHAR" type="in">				
			<CFPROCPARAM dbvarname="P_GROUP_DESCR" value="#arguments.groupDescription#" null="true" cfsqltype="CF_SQL_LONGVARCHAR" type="in">
			<CFPROCPARAM dbvarname="P_STATUS" variable="Localscope.procSuccess" cfsqltype="CF_SQL_NUMERIC" type="out">
			<CFPROCPARAM dbvarname="P_MESSAGE" variable="Localscope.procMessage" cfsqltype="CF_SQL_VARCHAR" type="out">
		</CFSTOREDPROC>	


		<cfdump var="#Localscope#" />

 

 

Stored Proc

create table org_demographics ( 
    name VARCHAR2(255 BYTE), 
    descr VARCHAR2(255 BYTE) 
);

create table party_roles ( 
    pr_seq_no NUMBER(12,0), 
    identifier VARCHAR2(32 BYTE) 
);

create or replace package testPackage 
as 
    procedure testProc ( 
            p_nt_user_id					IN	VARCHAR2, 
            p_group_pr_seq_no				IN	party_roles.pr_seq_no%TYPE, 
            p_group_name					IN	org_demographics.name%TYPE, 
            p_parent_cbo_pr_seq_no			IN	party_roles.pr_seq_no%TYPE, 
            p_group_manager_pr_seq_no		IN	party_roles.pr_seq_no%TYPE, 
            p_group_id						IN	party_roles.identifier%TYPE, 
            p_group_descr					IN	org_demographics.descr%TYPE, 
            p_status						OUT NUMBER, 
            p_message						OUT	VARCHAR2 
    );   
end;

 

    This topic has been closed for replies.
    Correct answer Charlie Arehart

    That is what I suspected. So first, do your cfprocparam lines have a dbvarname attribute? If so, read on.

     

    Those had been ignored by cf for several years. Then as of update 3 of cf11, cf started honoring that. And a problem with that is that in the years before then, jdbc drivers called for any reference to such var names to be prefixed. In the case of oracle, that would be a ":".  To be clear, this was not Adobe's choice, but the db vendors'.

     

    Another option is to remove the dbvarname attribute on cfprocparam--as long as your cfprocparams are offered in the order the arguments are defined in the sp (in oracle). 

     

    As for why this is biting you now, perhaps your cf11 itself was not updated beyond its u3 because of this issue.

     

    Or if it was, note that back in cf11 a JVM arg was introduced by Adobe to work-around the problem, but that was arg not carried into (and is ignored by) cf2016 and above. 

     

    (I saw your attachments message arrive but I'm reading this on a phone so will forego downloading/looking at that.)

     

    Let us know if this helps, even if you try it with only one failing cfstoredproc call.

     

    For more on this, see a 2015 post from adobe, and I also cover it in a talk I did on migrating apps to cf2021 (and I cover things of benefit to those moving to cf2018 as well), offered at carehart.org/presentations. 

    2 replies

    Participating Frequently
    February 8, 2023
    Charlie Arehart
    Community Expert
    Community Expert
    February 8, 2023

    What version of cf were you on when your code worked, before you're move to cf2021? 

    /Charlie (troubleshooter, carehart. org)
    Participating Frequently
    February 8, 2023

    it worked on CF9 and CF10..
    Then we jumpted to CF2018, it did not work.. And i tested on CF2019 and its not working either..


    Its a legacy application and the SP datatypes are the way they are when the schema was created.
    So trying to avoid a massive schema change at get it to work with the current datatypes in Oracle.

     

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity ExpertCorrect answer
    Community Expert
    February 8, 2023

    That is what I suspected. So first, do your cfprocparam lines have a dbvarname attribute? If so, read on.

     

    Those had been ignored by cf for several years. Then as of update 3 of cf11, cf started honoring that. And a problem with that is that in the years before then, jdbc drivers called for any reference to such var names to be prefixed. In the case of oracle, that would be a ":".  To be clear, this was not Adobe's choice, but the db vendors'.

     

    Another option is to remove the dbvarname attribute on cfprocparam--as long as your cfprocparams are offered in the order the arguments are defined in the sp (in oracle). 

     

    As for why this is biting you now, perhaps your cf11 itself was not updated beyond its u3 because of this issue.

     

    Or if it was, note that back in cf11 a JVM arg was introduced by Adobe to work-around the problem, but that was arg not carried into (and is ignored by) cf2016 and above. 

     

    (I saw your attachments message arrive but I'm reading this on a phone so will forego downloading/looking at that.)

     

    Let us know if this helps, even if you try it with only one failing cfstoredproc call.

     

    For more on this, see a 2015 post from adobe, and I also cover it in a talk I did on migrating apps to cf2021 (and I cover things of benefit to those moving to cf2018 as well), offered at carehart.org/presentations. 

    /Charlie (troubleshooter, carehart. org)