Re: How to debug Designer generated code

Can you look for lines where a value is assigned to :new.created_date? (Note this can't be in an AFTER trigger.) ":new.CREATED_DATE := cg$rec.CREATED_DATE;" looks likely. What is cg$PATIENT.cg$row_type?

Or, compile everything with debug and try the update statement through a debugger.

Is the code maintained through Designer?

-----Original message-----
From: Yong Huang
Date: 29/9/08 22:44
> 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;
>
>

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