Home » SQL & PL/SQL » SQL & PL/SQL » identify exception
identify exception [message #189505] Thu, 24 August 2006 23:13 Go to next message
kishore2y
Messages: 14
Registered: April 2006
Location: chennai
Junior Member

hi,


plz tell me i have faced one interviewer the question is



in a pl/sql block ihave 3 statements insert,update,delete


while iam excuting means it will gives me error so how can i identify which statement gives error

thanks,
kishore
Re: identify exception [message #189563 is a reply to message #189505] Fri, 25 August 2006 02:09 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Kishore,
you can use dbms_output.put_line between the DML statements.
for example, in my table tab2 "A" is primary key


SQL> SELECT * FROM TAB2;

         A B                   C
---------- ---------- ----------
         1 A1                 10
         2 B1                 10
         3 C1                 10
         4 A2
         5 B2

SQL> 
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
INSERT INTO TAB2 VALUES(6,'C2',40);
DBMS_OUTPUT.PUT_LINE('ROW INSERTED');
DELETE FROM TAB2 WHERE A=5;
DBMS_OUTPUT.PUT_LINE('ROW DELETED');
UPDATE TAB2 SET A=3 WHERE A=6;
DBMS_OUTPUT.PUT_LINE('ROW UPDATE');
END;  2    3    4    5    6    7    8  
  9  /
ROW INSERTED
ROW DELETED
begin
*
ERROR at line 1:
ORA-00001: unique constraint (CR_PROC.SYS_C001491693) violated
ORA-06512: at line 6


here from the output itself, you can get know where was the error occured that is i did not get the message "row update" as output.this means that, the error was occured in update statement.

Thanks,
Thangam
Previous Topic: Problem with cost based optimizer?
Next Topic: how to find no of bytes for each word or character in Oracle
Goto Forum:
  


Current Time: Sat Dec 10 10:37:26 CST 2016

Total time taken to generate the page: 0.11332 seconds