ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1
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;
