Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL*Plus IF / THEN / EXIT method

Tips and Tricks

SQL*Plus IF / THEN / EXIT method

by  nbateman  Posted    (Edited  )
The requirement is to be able to exit SQL*Plus depending on a condition tested within the script.

The problem is that the IF/THEN/ELSE construct is for PL*SQL, not SQL*Plus and the SQL*Plus command exit is not allowed. For example, the following does not work:
Code: 
begin
   IF :returncode != 0 THEN
      exit :returncode;
   END IF;
end;

The simplest solution requires two files to be set up in advance:
1. exit.sql containing the single line "exit &1"
2. continue.sql which will be empty


The following can then be used to either continue the script (when 0 returned) or exit (when 0 NOT returned):
Code: 
COLUMN returnvalue NEW_VALUE returnvalue
COLUMN action NEW_VALUE action  

SELECT function() AS returnvalue FROM dual;

SELECT decode(&&returnvalue , 0, 'continue', 'exit') AS action FROM dual;

@&&action &&returnvalue
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top