Conditional branching in SQL*Plus scripts.April 28, 2010 at 4:30 pm | Posted in Oracle Developement | 4 Comments
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
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