Re: How to debug Designer generated code

Sorry. That trigger is disabled. There's another trigger, enabled, after-update 
type, which is 300 line long:

E:\>grep -i created_date CG$AUS_PATIENT.lst
                    cg$old_rec.CREATED_DATE := 
cg$PATIENT.cg$table(idx).CREATED_DATE;
                    cg$rec.CREATED_DATE := 
cg$PATIENT.cg$table(idx).CREATED_DATE;
                    cg$ind.CREATED_DATE := updating('CREATED_DATE');

It looks like the created_date column is not updated in spite of these 
complicated variable assignment. (It makes sense because an update SQL should 
update updated_date.) Anyway. If anybody has general suggestions on debugging 
this type of code, please advise.

Yong Huang

--- On Mon, 9/29/08, Yong Huang <yong321@xxxxxxxxx> wrote:

> From: Yong Huang <yong321@xxxxxxxxx>
> Subject: How to debug Designer generated code
> To: oracle-l@xxxxxxxxxxxxx
> Date: Monday, September 29, 2008, 4:44 PM
> 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


Other related posts: