SQLCODE and SQLERRM
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQLCODE and SQLERRM

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    100
    Hi all,

    I write the code below in order to recuperate the error messages in a table(db_errors) I created. But nothing is inserted in the db_errors table even the statements are wrong. Please advice how to use SQLCODE and SQLERRM.
    This is executed in SQL*Plus

    DECLARE
    err_num NUMBER;
    err_msg VARCHAR2(100);
    BEGIN
    INSERT INTO ...
    INSERT INTO ...
    UPDATE…

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    err_msg := SUBSTR(SQLERRM(SQLCODE), 1, 250);
    INSERT INTO db_errors(err_msg) VALUES (err_msg);
    END;
    /

    Thanks in advance


  2. #2
    Join Date
    Feb 2001
    Posts
    180
    I'm not able to try it at this moment, but I think it's the sequence of commands:

    EXCEPTION
    WHEN OTHERS THEN
    -- First save the message
    err_msg := SUBSTR(SQLERRM, 1, 100); -- << as defined above
    ROLLBACK;
    INSERT INTO db_errors(err_msg) VALUES (err_msg);
    commit;
    END;
    Regards
    Ben de Boer

  3. #3
    Join Date
    Feb 2001
    Posts
    100

    Hi,

    Thanks ben for your reply,
    I changed the sequence but no way.

  4. #4
    Join Date
    Jul 2000
    Posts
    243
    do this

    tm_error varchar2(2000);
    tm_message varchar2(2000);

    tm_error := SQLCODE;
    tm_message := SQLERRM (tm_error);
    dbms_output.put_line('The Oracle Error is: '||tm_message);

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    sorry

    in the declare part:
    tm_error varchar2(2000);
    tm_message varchar2(2000);

    dbms_output.put_line('before tm_error: '||tm_error);
    tm_error := SQLCODE;
    dbms_output.put_line('after tm_error: '||tm_error);

    dbms_output.put_line('before tm_message: '||tm_message);
    tm_message := SQLERRM (tm_error);
    dbms_output.put_line('after tm_message: '||tm_message);
    dbms_output.put_line('before tm_message_2: '||tm_message_2);
    tm_message_2 := SUBSTR(tm_message_2, 1, 250);
    dbms_output.put_line('aftere tm_message: '||tm_message_2);

    INSERT INTO db_errors(err_msg) VALUES (tm_message_2);

    this way you can check on your results on the way. i can only tell you that i think it will work.

  6. #6
    Join Date
    Feb 2001
    Posts
    100
    Thanks all,

    My script works. I was testing it with syntax error instead of simulating an execution error.

  7. #7
    Join Date
    Jul 2000
    Posts
    296
    Variable err_msg has length of 100 and with
    SUBSTR(SQLERRM(SQLCODE), 1, 250) err_msg can be too small.

    Try:

    DECLARE
    err_num NUMBER;
    err_msg VARCHAR2(100);
    BEGIN
    INSERT INTO ...
    INSERT INTO ...
    UPDATE…

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    err_msg := SUBSTR(SQLERRM(SQLCODE), 1, 100);
    INSERT INTO db_errors(err_msg) VALUES (err_msg);
    END;
    /

Posting Permissions

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



Click Here to Expand Forum to Full Width