Re: When inserting a bad record, send the error record to another table with Oracle Error

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Thu, 10 Jan 2008 04:22:02 GMT
Message-ID: <K3hhj.63237$Rw3.18352@newsfe06.phx>

zip <zipRoberts_at_gmail.com> wrote in
news:b132e958-4815-4b4a-ac9b-79546ddaa517_at_p69g2000hsa.googlegroups.com:

> Say I have a table SCOTT.EMP and when someone inserts into the table
> and incurrs and error (Violated PK, FK constraints, etc) I want that
> record to write to an EMPAUDIT table so I can review it. I wrote
> this trigger that I thought would do the job, but it does not. My
> next step would be to add a field to EMPAUDIT called ERRMSG and write
> the SQLERRM to the table too. Just trying to get this to work first.
>
>
> CREATE OR REPLACE TRIGGER empTrg
> AFTER INSERT
> ON emp
> FOR EACH ROW
> BEGIN
> NULL;
>
> EXCEPTION
> WHEN others then
> INSERT INTO empaudit(EMPNO, ENAME, JOB, MGR,HIREDATE, SAL, COMM,
> DEPTNO ) VALUES
> (:new.EMPNO, :new.ENAME, :new.JOB, :new.MGR,:new.HIREDATE, :new.SAL,
> :new.COMM, :new.DEPTNO);
>
> END empTrg ;
> /
>
> Here is EMPAUDIT
>
> EMPNO
> ENAME
> JOB
> MGR
> HIREDATE
> SAL
> COMM
> DEPTNO
> AUDITDATE
> AUDITCODE
>
>
> The trigger compiles, a PK violation shows up in SQLPLUS, but the
> record does not go to EMPAUDIT with this trigger.

With V10 or high you can CREATE TABLE ...... LOGGING ....... & specify a logging table.

All Failied INSERT or UPDATE records will be written into this table.

http://download.oracle.com/docs/cd/B19306_ 01/server.102/b14200/statements_7002.htm#i2129672

CREATE TABLE print_media_new

    ( product_id        NUMBER(6)
    , ad_id             NUMBER(6)
    , ad_composite      BLOB
    , ad_sourcetext     CLOB
    , ad_finaltext      CLOB
    , ad_fltextn        NCLOB
    , ad_textdocs_ntab  textdoc_tab
    , ad_photo          BLOB
    , ad_graphic        BFILE
    , ad_header         adheader_typ
    , press_release     LONG

    ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_new     LOB (ad_sourcetext, ad_finaltext) STORE AS

      (TABLESPACE example
       STORAGE (INITIAL 6144 NEXT 6144)
       CHUNK 4000
       NOCACHE LOGGING);

Received on Wed Jan 09 2008 - 22:22:02 CST