ORACLE CLINICAL RESPONSE ID LENGTH GREATER THAN 10 MAKES FAILURE IN VALIDATION PROCEDURES (Doc ID 1067344.1)

Last updated on FEBRUARY 12, 2019

Applies to:

Symptoms

ISSUE:
-------
The response_id column size is number (15), but this is not reflected in procedures since a Lag variable size is number(10).

STEPS TO REPRODUCE:
-----------------------------
1.) Create a Validation Procedure using a one question DCM called 'LAG'.
2.) [Q-Grps] > [Questions] > set the Lag Depth = 2
3.) Generate the procedure.
4.) Enter a new document for the DCI which contains DCM used in step 1.
5.) SQLPLUS :
now manually update the response_id so that the id length is greater than ten. This will mimic a production environment where the length of the current response_id is greater than ten
.
select response_id,value_text,end_ts
from responses
where received_dcm_id = (select received_dcm_id
from received_dcms
where document_number ='LAG')
.
RESPONSE_ID VALUE_TEXT END_TS
----------- ----------- ---------
2014003 1 15-AUG-01
.

(RESPONSE_ID -> 2014003 is just an example to have a clear overview)
.
update responses
set response_id = 99999999999 -- 11 chars
where response_id = 2014003 ;
.
commit ;
.
.
6.) DE > Update : Change value_text from 1 to 2
7.) Execute Validation Procedure > the following error occurs in the log file :

Error executing: Post Details
Patient: 1
Message: ORA-6502: PL/SQL: numeric or value error: number precision too large
8. ) Edit Procedure :

Change :
A$SNUM$I1 number(10);
A$SNUM$I2 number(10);
.
To:
A$SNUM$I1 number(11);
A$SNUM$I2 number(11);

9. ) Now re-execute procedure - completes successfully.

BUSINESS IMPACT:
-----------------------
Business impact : ongoing studies with validation procedure using lag option cannot run correctly.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.