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…

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:

@&v_script_name

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

DECLARE
l_yes_no varchar2(1) := lower(trim('&v_yes_no'));
BEGIN
IF l_yes_no = 'y'
THEN
-- Do something
ELSE
-- Do something else
END IF;
END;
/

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

Leave a Reply

Your email address will not be published. Required fields are marked *