if you want to rollback / do some extra stuff in the case of an ORA code, then do it all in the SQL*PLUS session.
i.e. run it as a script.
set serverout on
begin
update...;
exception
when others -- others is a catch all, you can catch specific codes too
then
rollback;
dbms_output.put_line('Error!');
dbms_output.put_line(sqlerrm); -- prints full error string
end;
/
if you just want to signal to bash that a sql statement failed , you can just set as the first thing in sql*plus. whenever sqlerror exit sql.sqlcode (or whenever sqlerror (exit -1 etc) instead (see here). This would stop on the first error and return to your schell script with an appropriate return code.
you can nest blocks eg:
begin
update ..;
begin
select id into v_id
from tab
where ...;
exception
when no_data_found
then
null;-- ignore that we didnt find a row
end;
-- if the select fails, we continue from here..
delete...;
begin
savepoint mysave;
your_proc(...);
exception
when others
then
rollback to mysave; -- of the call to your_proc fails, lets just toll that back alone
end;
end;
etc.
if you needed it to be interactive, you could do something like (dbms_alert)[http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_alert.htm#CHDCFHCI]:
sqlplus /<<EOF | tee -a my.log
set feedback on verify on serverout on
-- IE YOUR CODE HERE..
select * from dual;
begin
null;
end;
/
-- END OF YOUR CODE..
-- now lets wait for an alert from another session:
declare
v_message varchar2(32767);
v_status number;
begin
DBMS_ALERT.REGISTER('should_i_commit');
DBMS_ALERT.WAITONE('should_i_commit', v_message, v_status); -- there is a timeout parameter you can set too
if (v_message = 'Y')
then
dbms_output.put_line('I committed');
commit;
else
dbms_output.put_line('I rolled back');
rollback;
end if;
end;
/
EOF
then in another session you can issue:
SQL> exec dbms_alert.signal('should_i_commit', 'N');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
