SQL & PL/SQL

Hi I want to use raise_application_error or raise to send a error to the parent block.

But in this case a implicit rollback is executed why ?

For exemple, for me on this sample, I must always see the row 'A' !
set serverout on

create table t_sygale(col1 varchar2(1));

select * from t_sygale;

begin
dbms_output.put_line('Trace A');
insert into t_sygale(col1) values('A');
dbms_output.put_line('Trace AA');
insert into t_sygale(col1) values('AB');
exception
when others then
dbms_output.put_line('OTH:'||sqlerrm);
end;
/

select * from t_sygale;

rollback;

select * from t_sygale;

begin
dbms_output.put_line('Trace A');
insert into t_sygale(col1) values('A');
dbms_output.put_line('Trace AA');
insert into t_sygale(col1) values('AB');
exception
when others then
dbms_output.put_line('OTH:'||sqlerrm);
raise;
end;
/

select * from t_sygale;

SQL> set serverout on
SQL> create table t_sygale(col1 varchar2(1));

Table created. good

SQL> select * from t_sygale;

no rows selected ok

SQL> begin
2 dbms_output.put_line('Trace A');
3 insert into t_sygale(col1) values('A');
4 dbms_output.put_line('Trace AA');
5 insert into t_sygale(col1) values('AB');
6 exception
7 when others then
8 dbms_output.put_line('OTH:'||sqlerrm);
9 end;
10 /
Trace A
Trace AA
OTH:ORA-12899: value too large for column "SYGALE"."T_SYGALE"."COL1" (actual: 2, maximum: 1)

PL/SQL procedure successfully completed.

ok the dbms_output have log the error and the procedure exit succesfully

SQL>
SQL> select * from t_sygale;

C
-
A

OK the statement have an error on the insert into with 'AB' but we can see on my table the row with 'A' value

SQL> rollback;

Rollback complete. new test

SQL> select * from t_sygale;

no rows selected rollback is executed

SQL> begin
2 dbms_output.put_line('Trace A');
3 insert into t_sygale(col1) values('A');
4 dbms_output.put_line('Trace AA');
5 insert into t_sygale(col1) values('AB');
6 exception
7 when others then
8 dbms_output.put_line('OTH:'||sqlerrm);
9 raise;
10 end;
11 /
Trace A
Trace AA
OTH:ORA-12899: value too large for column "SYGALE"."T_SYGALE"."COL1" (actual: 2, maximum: 1)
begin
*
ERROR at line 1:
ORA-12899: value too large for column "SYGALE"."T_SYGALE"."COL1" (actual: 2, maximum: 1)
ORA-06512: at line 9

Ok the dbms_output and the error is sended to SQL*PLUS

SQL>
SQL> select * from t_sygale;

no rows selected Why the row 'A' not appear

SQL>

I don't understand why I can't see on my table the row with 'A' value ?

SQL*PLUS or TAOD could done a implicit rollback but where is the option ?


In the Oracle Documentation
How Oracle Does Implicit Rollbacks
Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction. If the statement raises an unhandled exception, the host environment determines what is rolled back.

On Internet
If an exception is raised in your program and it is not handled by an exception section in either the current or enclosing PL/SQL blocks, that exception is unhandled. PL/SQL returns the error that raised the unhandled exception all the way back to the application environment from which PL/SQL was run. That environment (a tool like SQL*Plus, Oracle Forms, or a Java program) then takes an action appropriate to the situation; in the case of SQL*Plus, a ROLLBACK of any DML changes from within that top-level block's logic is automatically performed.

Message was edited by:
Sygale