Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    30

    Unanswered: Exception Handler question

    Hi

    Im running this bit of code to handle errors, ive run it against dummy data to test it as follows:

    insert into ecoes_data (mpan_core, reg_efs_date) values ('1234567891235', '30-FEB-98');
    EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises', 'errlog');
    INSERT INTO error_log (mpan) values ('error')
    LOG ERRORS INTO error_log ('ORA_ERR_NUMBER$') REJECT LIMIT 100;

    I deliberately inserted the date as 30th Feb to test the error handler. It works, and the table error_log updates with the value "error" so it is working. However, where im having trouble is I want to insert all the values in the insert values section (values ('1234567891235', '30-FEB-98')) so that once ive completed inserting my data, i can see which rows data have failed.
    If that makes sense. In a nutshell, I want to be able to look in error_log and see which data has failed rather than just the "error" ive put as a default test value.

    Any help is appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to be able to look in error_log and see which data has failed rather than just the "error" ive put as a default test value.

    Make sure your eyes are open when doing SELECT to see the data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2010
    Posts
    5
    i'm not sure this helps as he is trying to do an insert as oppose to a select?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Ben_work View Post
    i'm not sure this helps as he is trying to do an insert as oppose to a select?
    OP needs to clarify more precisely what the problem is & what is the desired solution
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2009
    Posts
    30
    OK,
    Im running this.

    insert into ecoes_data (mpan_core, reg_efs_date) values ('1234567891235', '30-FEB-98');
    EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises', 'errlog');
    INSERT INTO error_log (mpan) values ('error')
    LOG ERRORS INTO error_log ('ORA_ERR_NUMBER$') REJECT LIMIT 100;

    This is the result of trying to insert the values above:
    ora_err_number$ ora_err_mesg$ ora_err_rowid$ ora_err_optyp$ ora_err_tag$ mpan
    (null) (null) (null) (null) (null) error

    I want to see in the error_log table is “ORA-ERROR 01839,'1234567891235', '30-FEB-98'” so I know exactly which data item has failed, and the item that has failed.

    Easier to understand?

    Thanks

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Then, what about create error login table over the table you are inserting (ECOES_DATA, not RAISES) and use LOG ERRORS clause in the INSERT statement you are interested in? Something like (not tested, consult documentation for exact syntax):
    Code:
    begin
      DBMS_ERRLOG.CREATE_ERROR_LOG(
        dml_table_name=>'ECOES_DATA',
        err_log_table_name=>'ERROR_LOG'
      );
    end;
    /
    
    insert into ecoes_data (mpan_core, reg_efs_date)
    values ('1234567891235', '30-FEB-98')
    LOG ERRORS INTO error_log ('ORA_ERR_NUMBER$')
    REJECT LIMIT 100;
    As now, you created log table over RAISES table which was nowhere used. Additionally, you were logging errors from direct insert into ERROR_LOG table (which probably succeeded, so you could see its results). I have no idea, whether it was supposed to do what I advised above.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •