How to debug Designer generated code
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 29 Sep 2008 14:44:47 -0700 (PDT)
Oracle 10.2.0.4 on Linux x86-64; Designer 10.1.2.2
The application sometimes has the following error:
Oracle Error Message:
ORA-12899: value too large for column "ODS_OWNER"."PATIENT"."CREATED_DATE"
(actual: 8292, maximum: 7)
SQL Statement:
UPDATE PATIENT SET ... WHERE (PRK = '860019')
Sometimes the same SQL has this error:
ORA-12899: value too large for column "ODS_OWNER"."PATIENT"."CREATED_DATE"
(actual: 16484, maximum: 7)
...
The SQL is not using bind variables. The DATE type column created_date is not
in the SQL. It's updated (if applicable) by a 439-line before-update trigger:
DECLARE
cg$rec cg$PATIENT.cg$row_type;
cg$ind cg$PATIENT.cg$ind_type;
cg$old_rec cg$PATIENT.cg$row_type;
BEGIN
-- Application_logic Pre-Before-Update-row <<Start>>
-- Application_logic Pre-Before-Update-row << End >>
-- Load cg$rec/cg$ind values from new
...
cg$rec.CREATED_DATE := :new.CREATED_DATE;
cg$ind.CREATED_DATE := (:new.CREATED_DATE IS NULL AND :old.CREATED_DATE
IS NOT NULL )
OR (:new.CREATED_DATE IS NOT NULL AND :old.CREATED_DATE
IS NULL)
OR NOT(:new.CREATED_DATE = :old.CREATED_DATE) ;
cg$PATIENT.cg$table(cg$PATIENT.idx).CREATED_DATE := :old.CREATED_DATE;
...
:new.CREATED_DATE := cg$rec.CREATED_DATE;
...
Those cg$ packages are created by Designer. The DBA who (with two Oracle
consultants) wrote the code is no longer here. Since the error occurs at
unpredictable times and the app can always resubmit to get it to work, I don't
want to enable SQL trace and wait for the error. Metalink has articles about
ORA-12899 but not specific to date column (which limits to 7 bytes). Short of
reading the code from the base packages on, is there any general advice on how
to troubleshoot?
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
