SQLCODE and SQLERRM

[Hakimca]

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

[bensr]

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;

[Hakimca]

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

[shawish_sababa]

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);

[shawish_sababa]

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.

[Hakimca]

Thanks all,

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

[akkerend]

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;
/