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

On Jan 10, 5:22 am, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> zip <zipRobe..._at_gmail.com> wrote innews: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);
Hi Ana,
I know what you mean but the documentation reference and snippet are not referring to this feature.
Please see e.g. http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php and http://www.psoug.org/reference/dbms_errlog.html, and the DML seems to be like:
INSERT|UPDATE|DELETE|MERGE ... ... LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

Regards,
Erik Ykema Received on Thu Jan 10 2008 - 06:19:51 CST