[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How retrieve the statement error

Posted on 2007-10-04
5
Medium Priority
?
740 Views
Last Modified: 2013-12-19
Hi,
When an oracle error occurs  beyond to retrieve the Error Code and Error Message with SQLCODE and SQLERRM, I'd like to retrieve also the statement that produces this error.

For example:

CREATE TABLE TAB1
(
  ID    NUMBER,
  NAME  VARCHAR2(32 BYTE)
)

CREATE TABLE TAB2
(
  ID    NUMBER,
  NAME  VARCHAR2(32 BYTE)
)

CREATE TABLE LOG_ERROR
(
  OBJ_NAME  VARCHAR2(50 BYTE),
  ERR_CODE  NUMBER,
  ERR_MSG   VARCHAR2(500 BYTE),
  STM_MSG  VARCHAR2(1000 BYTE)
)

 I've this trigger:

CREATE OR REPLACE TRIGGER MY_TRG
BEFORE INSERT ON TAB1
FOR EACH ROW
DECLARE
    ERR_NUM        NUMBER;
    ERR_MSG        VARCHAR2(300);

BEGIN
IF :NEW.ID=3 THEN
INSERT INTO TAB2 (ID, NAME) VALUES ('AAA', 5555);
END IF;

IF :NEW.ID = 7 THEN
INSERT INTO TAB2 (ID, NAME) VALUES ('BBBB','7777');
END IF;

EXCEPTION
WHEN OTHERS THEN
          ERR_MSG:= SUBSTR(SQLERRM, 1, 300);
          ERR_NUM:= SQLCODE;
         INSERT INTO LOG_ERROR (OBJ_NAME, ERR_CODE, ERR_MSG)
              VALUES ('MY_TRG', ERR_NUM, ERR_MSG);
END MY_TRG;


When I RUN:

INSERT INTO TAB1 (ID, NAME) VALUES (3, 'MY_NAME');
COMMIT;

I get this error in LOG_ERROR table:
OBJ_NAME.............ERR_CODE...............ERR_MSG.....................STM_MSG
MY_TRG...............-1722..............ORA-01722: invalid number.............


INSERT INTO TAB1 (ID, NAME) VALUES (7, 'YOUR_NAME');
COMMIT;

I get same error in LOG_ERROR table:
OBJ_NAME.............ERR_CODE...............ERR_MSG.....................STM_MSG
MY_TRG...............-1722..............ORA-01722: invalid number.............


I'd like to get:

INSERT INTO TAB1 (ID, NAME) VALUES (3, 'MY_NAME');
COMMIT;

OBJ_NAME.............ERR_CODE...............ERR_MSG..........................STM_MSG
MY_TRG...............-1722..............ORA-01722: invalid number.......INSERT INTO TAB2 (ID, NAME) VALUES ('AAA', 5555)


INSERT INTO TAB1 (ID, NAME) VALUES (7, 'YOUR_NAME');
COMMIT;

OBJ_NAME.............ERR_CODE...............ERR_MSG..........................STM_MSG
MY_TRG...............-1722..............ORA-01722: invalid number.......INSERT INTO TAB2 (ID, NAME) VALUES ('BBBB','7777')

How can I retrieve the statement that produces this error in the STM_MSG column?

Thanks in advance!
0
Comment
Question by:dba_ad_oracle
  • 2
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 20016713
The SQL that produced the error isn't stored with the error condition anywhere, because it might not be sql that did it.  If your session is killed for instance, it's not the sql statement that did it.

The only way to do what you're looking for is to save the statement in a variable prior to executing it then write that variable out in your exception handler

something like this....

declare
v_stmt varchar2(100);
begin
v_stmt := 'INSERT INTO TAB1 (ID, NAME) VALUES (3, ''MY_NAME'');'
INSERT INTO TAB1 (ID, NAME) VALUES (3, 'MY_NAME');
COMMIT;
exception
when others then
dbms_output.put_line('Error Message: ' || sqlerrm);
dbms_output.put_line('Offending Statement: ' || v_stmt);
endl
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22198741
I believed I've answered the "why" of the question, as well as provided a viable example of an alternative.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question