Hi everyone,

my question is about how to correctly handling exception in a pl/sql procedure: I need to rollback everything made in a begin-end block if there's any kind of exception.

Here's the example code:

create table prova (cod number);
alter table
add constraint pk_prova primary key (cod)
;


create or replace procedure prova_1
is
l_err_num number;
l_err_desc varchar2(100);

begin
insert into prova values(1);
insert into prova values(1);
insert into prova values(2);
commit;

EXCEPTION
    WHEN OTHERS THEN
        l_err_num := SQLCODE;
        l_err_desc := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(l_err_num ||': '||l_err_desc);
        RAISE;
commit;

end; 

As i compile and execute the procedure, i can see that with the "RAISE" command in the exception block, i find no row in the prova table. Instead, taking off the "raise" command, i find a 1 in the table...
Is this the correct way to use the "RAISE" command? Or there's a better way to handle the rollback?

For better undestanding: in the real procecdure there's no dbms_output but an insert in a log table.

Many thanks,
Gaia