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:

@&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;
/