2 Replies Latest reply: Nov 19, 2012 3:18 AM by don123 RSS

    dbms error log


      i am trying to create error log, but the results are not correct.

      i am inserting emp records in emp2, i have check constraints salary greater than 5000 in emp2 table

      SQL> insert into emp2 (id, name, sal) select id, name, sal from emp log errors into error_log_emp2;

      i have created error_log_emp2 table by using dbms_errlog.create_error_log procedure.

      but, in error_log_emp2 table only three records inserted instead of six records listed which violate check constraint, and no records inserted in emp2 which will not violate check constraint.

      Please help with your suggestions on using error logging.....

      SQL> select id, name, sal from emp where sal>4990;
      ---- -------------------- -----
      8 LAKSHMI 5000
      9 HEMA 5000
      10 CHANDRA 5000
      11 KESHAVA 5000
      12 GEETA 6000
      101 JUNK 5000

      SQL>select * from error_log_emp2;

      "ORA-02290: check constraint (AMDB.CHECK_SAL) violated "          I          8     LAKSHMI     5000
      "ORA-02290: check constraint (AMDB.CHECK_SAL) violated"          I          9     HEMA     5000
      "ORA-02290: check constraint (AMDB.CHECK_SAL) violated"          I          8     LAKSHMI     5000
        • 1. Re: dbms error log
           log errors into error_log_emp2 reject limit unlimited;
          the default reject limit is 0, your code will error out when the first error occurs..

          probably you are having 3 rows in error log table coz you ran the inser statements 3 times. Note taht, error logging (only the logging part) is autonomous and auto commiting.

          Edited by: jeneesh on Nov 19, 2012 11:50 AM
          • 2. Re: dbms error log
            Many thanks