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

[adobe_mayur]

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;

[Charlie Arehart]

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

[adobe_mayur]

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 · Accepted solution]

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. 

[adobe_mayur]

Wow, that worked.

I just remove the dbvarname and made my CFSTOREDPROC look like this. And it worked.

Thanks a lot for you assistance.. definitely saved me a few days.

<CFSTOREDPROC procedure="CHARGE.ROPS_HIER_APP.HIER_CREATE_GROUP" datasource="#dsn#">
<CFPROCPARAM value="#arguments.userName#" cfsqltype="CF_SQL_VARCHAR" type="in">
<CFPROCPARAM value="" null="true" cfsqltype="CF_SQL_INTEGER" type="in">
<CFPROCPARAM value="#Arguments.groupName#" cfsqltype="CF_SQL_VARCHAR" type="in">
<CFPROCPARAM value="#Arguments.cboID#" cfsqltype="CF_SQL_INTEGER" type="in">
<CFPROCPARAM value="#Arguments.groupLead#" null="#(len(Arguments.groupLead) IS 0)#" cfsqltype="CF_SQL_VARCHAR" type="in">
<CFPROCPARAM value="#Arguments.groupIdentifier#" cfsqltype="CF_SQL_VARCHAR" type="in">
<CFPROCPARAM value="#Arguments.groupDescription#" cfsqltype="CF_SQL_LONGVARCHAR" type="in">
<CFPROCPARAM variable="Localscope.procSuccess" cfsqltype="CF_SQL_NUMERIC" type="out">
<CFPROCPARAM variable="Localscope.procMessage" cfsqltype="CF_SQL_VARCHAR" type="out">
</CFSTOREDPROC>

[Charlie Arehart]

Great to hear, and thanks for marking my reply as the answer. Hope it may help others.

And sorry that I see now your very first post even had the CFML AND the stored procedure code. 🙂 Again, as I was responding on the phone so that just was not as apparent as it is now that I look at this on my computer. Oh well, some good news is that the "answer" as it is will perhaps stand better for any who may find it, to help them look more closely at their own code.

(I also plan to do a post on the topic, because you're really not alone in tripping over it. Many are only moving to such later CF versions from such older ones, for various reasons. Hope the rest of your move goes smoothly. Again, see my webinar for still more that may help in the migration.)