How to debug Designer generated code

From: Yong Huang <yong321_at_yahoo.com>
Date: Mon, 29 Sep 2008 14:44:47 -0700 (PDT)
Message-ID: <302232.68993.qm@web80605.mail.mud.yahoo.com>

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       

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 29 2008 - 16:44:47 CDT