Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rollback on Exception

sql_coderJan 28 2013 — edited Feb 11 2013
Hello Oracle Community,

I have the following stored procedure and exceute it in an anonymous block. T1 is a very simple and empty test table with the first column as a number and primary key constraint and the second column with a varchar datatype.
CREATE OR REPLACE PROCEDURE p_test 
AS 
BEGIN
    INSERT INTO t1 VALUES (1, 'Testvalue p_test');
    RAISE_APPLICATION_ERROR(-20000, 'Error Message from p_test');
/*    
EXCEPTION 
  WHEN OTHERS THEN
    NULL;  
*/    
END p_test;

SET Serveroutput ON
BEGIN
  p_test(); 
/*
EXCEPTION 
  WHEN OTHERS THEN
    NULL;    
*/    
END;  
When I execute then anonymous block with an exception (either in the procedure or anymous block), I can see the record in table t1 (uncommited). When I remove all the Exceptions, the error goes through till the host enviroment (SQLDeveloper) and I cannot see any record in the test table. My question is, what is doing the rollback, is it host application ?

Ikrischer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 11 2013
Added on Jan 28 2013
24 comments
6,726 views