-
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
-
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
-
Hi,
Thanks ben for your reply,
I changed the sequence but no way.
-
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);
-
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.
-
Thanks all,
My script works. I was testing it with syntax error instead of simulating an execution error.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|