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

Advertisements

4 Comments »

RSS feed for comments on this post. TrackBack URI

  1. […] This is somewhat similar to an earlier post where I describe how to do conditional branching in […]

  2. Bravo!!
    brilliant work.. I was looking for something like this.. and thought wasnt achievable ..
    Thanks a lot!!

  3. Just needed that. Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: