Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to rollback and exit a psql script on error?

Tags:

oracle

plsql

I have a psql script that looks like this:

-- first set of statements
begin
sql statement;
sql statement;
sql statement;
exception
    when others then
    rollback
    write some output
    (here I want to exit the entire script and not continue on to the next set of statements)
end
/
-- another set of statements
begin
sql statement;
sql statement;
sql statement;
exception
    when others then
    rollback
    write some output
    (here I want to exit the entire script and not continue)
end
/
... and so on

Is it possible to exit the script and stop processing the rest of the script?

like image 978
dtc Avatar asked Dec 29 '22 18:12

dtc


2 Answers

Put the following lines at the top of your file:

WHENEVER OSERROR EXIT ROLLBACK
WHENEVER SQLERROR EXIT ROLLBACK

... and make sure you have a RAISE; at the end of your exception handlers.

like image 147
Vadim K. Avatar answered Jan 13 '23 17:01

Vadim K.


I tend to use raise_application_error when I want to halt execution and pass back a custom error code/message to the calling script:

http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/UsingRAISEAPPLICATIONERROR.htm

like image 20
aw crud Avatar answered Jan 13 '23 16:01

aw crud