Conditional branching in SQL*Plus scripts.

During the creation of an installation SQL*Plus script, i wanted to be able to do some conditional branching based on user input.
For example, i want to prompt the user whether he wants a certain feature installed…

Do you wish to install feature xyz?

Based on the response, the script would then run the code to install this feature into the database.

Since SQL*Plus does not allow this kind of interaction, I created a little workaround: I capture the user’s input, and then depending on what that input is, call one SQL script or another.

I did this by first capturing the user’s input with ACCEPT:

ACCEPT v_yes_no CHAR PROMPT "Do you wish to install feature xyz? "

Now that I have the user’s input in a substitution variable, I use DECODE to translate that variable into a SQL*Plus script name, and capture it in another substitution variable:

COLUMN script_name NEW_VALUE v_script_name
SET termout OFF --hide this from the user
SELECT decode(lower('&v_yes_no'),'y','xyz.sql','nothing.sql') script_name
FROM dual;
SET termout ON

If the user chose ‘y’ then v_script_name is xyz.sql, which is the script that contains the code to install this feature. If the user chose something other than ‘y’ (or ‘Y’), the v_script_name is nothing.sql. The script nothing.sql is simply an empty .sql file.

Now to execute the script, simply run it using the substitution variable v_script_name:


Another option is to branch inside a PL/SQL block by analyzing the v_yes_no substitution variable:

l_yes_no varchar2(1) := lower(trim('&v_yes_no'));
IF l_yes_no = 'y'
-- Do something
-- Do something else

4 thoughts on “Conditional branching in SQL*Plus scripts.

Comments are closed.