Conditional Execution and Conditional PL/SQL Compilation in SQL*Plus

Scripting with SQL*Plus is a must for any decent Oracle professional! Usually for simple day-to-day tasks, but quite often for even  more complex tasks, we have to implement our "program logic" with SQL*Plus commands, SQL statements and anonymous PL/SQL blocks, all embedded within an SQL*Plus script.

Quite often the need arises to include some "conditional execution" logic in our script. I.e., we want to execute only certain parts of the script according to some condition. This is also called "branching" and is analogous to an IF-THEN-ELSE construct in a programming language. The condition to be evaluated can change dynamically each time that our script is executed (e.g., because it is based on user input).

Moreover, there comes the time where for an anonymous PL/SQL block, embedded in a SQL*Plus script, you want to direct the compiler to omit a specific part of PL/SQL code, based on some condition (e.g., the Oracle version where the script runs, or some user-based input, etc.). This is called "conditional compilation" and is different than conditional execution.

In a programming language (as is PL/SQL) there exist conditional execution constructs (e.g., IF-THEN-ELSE), so conditional execution is not an issue.  What we want to achieve with conditional compilation is to make a part of PL/SQL code "invisible" to the compiler each time we run our script, based on some dynamic condition. For example we might want to avoid a compilation error because of the Oracle version that our script runs on, so we have to "hide" different parts of the code based on the Oracle version.

In traditional PL/SQL, this is achieved with conditional compilation directives, statements, and identifiers prefixed by a dollar sign ($) (read this great article for details how this is done in PL/SQL). The question is how can you do it for anonymous PL/SQL within a SQL*Plus script, where compilation takes place "on-the-fly"?

In this post, we will show how one can achieve both conditional execution and conditional PL/SQL compilation in an SQL*Plus script.

1. "Conditional Logic" in an SQL*Plus Script

Whenever you want to implement some conditional logic in your SQL*Plus script, then you will have to code the following steps somewhere in your script:

1. Set the dynamic condition, e.g., accept some user input and form the condition to be evaluated
2. Evaluate the condition, i.e.,"IF-THEN-ELSE logic"
3. Comment-out parts of code based on step 2

Lets consider a simple example. Lets say that the user is prompted for an answer from our script and if the answer is "yes" then message1 is displayed, and if the answer is "no"  then message2 is displayed. 

In this scenario, the first step corresponds to accepting the answer from the user and forming the conditions "answer = 'yes'" and "answer = 'no'" to be evaluated. The second step corresponds to the evaluation of these conditions. It is the step where we need some "IF-THEN-ELSE logic". Well, the most straight-forward way to do this in a SQL*Plus script is with plain old SQL! For example with a statement like the following:

select
      case
         when <CONDITION> then 'RETURN SOMETHING'
         else 'RETURN SOMETHING ELSE'
      end as condition_result
from dual;

Finally, for the 3rd step we will comment out this part of the code (i.e., for our example the code that prints the corresponding message) whose condition does not evaluate to TRUE.

We will start with PL/SQL conditional compilation and use the above simple example as a means for demonstrating the method.

2. Conditional PL/SQL Compilation in an SQL*Plus Script

According to our running example our code must accept some user input and form a condition. Then it must evaluate the condition and based on this evaluation it must comment out the corresponding part of PL/SQL code. In the following code snippet we have implemented our example with conditional compilation logic. 

-- A simple Example for PL/SQL conditional compilation within an SQL*Plus script -- suppress variable substitution messages set verify off -- step 1: Set dynamic condition, e.g., accept some user input and form the condition to be evaluated accept answer   prompt 'Give me an answer yes or no (y/n)?' -- step 2: Evaluate condition, i.e.,"IF-THEN-ELSE logic" col condition_result1  new_value _COMMENT_OUT_1st_PART col condition_result2  new_value _COMMENT_OUT_2nd_PART select       case          when '&&answer' = 'y' then ''          else '--'       end as condition_result1,       case          when '&&answer' = 'n' then ''          else '--'       end as condition_result2    from dual; -- step 3: Comment-out parts of code based on step 2 begin     &&_COMMENT_OUT_1st_PART  dbms_output.put_line('you have said ''yes''!');     &&_COMMENT_OUT_2nd_PART  dbms_output.put_line('you have said ''no''!'); end; /   undef answer undef _COMMENT_OUT_1st_PART undef _COMMENT_OUT_2nd_PART

For the 1st step, we define a substitution variable "answer" to store the user input and prompt the user for his input. So the answer is stored in the substitution variable and the conditions to be evaluated are the following: "'&answer' = 'y'" and "'&answer' = 'n'".
The second step is to evaluate the condition and thus we can use an SQL statement like the following:

select
      case
         when '&&answer' = 'y' then '' 
         else '--'
      end as condition_result1,
      case
         when '&&answer' = 'n' then '' 
         else '--'
      end as condition_result2  

from dual;

The result of the conditions' evaluation is returned in two columns: condition_result1 and condition_result2. If condition 1 is true, then the result is an empty string, else if it is false, then the result is the beginning of a comment "--". The same goes for condition 2.

In order to retain the results from the conditions evaluation query, we create two new substitution variables,  _COMMENT_OUT_1st_PART and _COMMENT_OUT_2nd_PART that will store the results of columns condition_result1 and condition_result2 respectively. This is achieved with the following SQL*Plus commands:

col condition_result1  new_value _COMMENT_OUT_1st_PART

col condition_result2  new_value _COMMENT_OUT_2nd_PART

So after the query is executed, these two substitution values will either store an empty string or the "--" string.

Finally, for the 3rd step, inside our PL/SQL block, we precede each line of code that we want to put under conditional compilation with the corresponding substitution variable. Like this:

&&_COMMENT_OUT_1st_PART  dbms_output.put_line('you have said ''yes''!');

Note that in order for this trick to work, the line must be in the context of a PL/SQL block. If this was a line outside PL/SQL (i.e., an SQL*Plus command, or a SQL statement in the SQL*Plus script), then SQL*Plus would complain about the appearance of the "&&_COMMENT_OUT_1st_PART" at the beginning of the line, with the following error:

SP2-0734: unknown command beginning "'&&_COMMEN..." - rest of line ignored.

Lets try out the script to see if it works:

nkarag@DWHPRD> @conditional_compilation
Give me an answer yes or no (y/n)?y

you have said 'yes'!
nkarag@DWHPRD> @conditional_compilation
Give me an answer yes or no (y/n)?n

you have said 'no'! 

Listing 1: Test script to demonstrate conditional compilation  in SQL*Plus.

3. Conditional Execution in an SQL*Plus Script

Now, if we want to do the same thing but not in the context of a PL/SQL block but directly with SQL*Plus commands, then things are a bit trickier. This is because as we have noted above, we cannot precede an SQL*Plus command with a substitution variable that will comment out the command or not, as we have done in PL/SQL context. In SQL*Plus the only choice we have is to call another SQL*Plus script (a "child script" i.e, a "sub-script") that will "print" the start of a comment or not, in order to comment out or not the commands of the parent script. This great idea is described in this post by Sayan Malakshinov.

Lets see how our running example can be implemented with this method:

-- A simple example of Conditional Execution in an SQL*Plus script accept answer   prompt 'Give me an answer yes or no (y/n)?' @if "'&answer' = 'y'"     select 'you have said yes' from dual; -- end if */ @if "'&answer' = 'n'"     select 'you have said no' from dual; -- end if */

Isn't it a beauty? As you can see in the above script we use another SQL*Plus script (one that is named "if.sql") to implement an "IF-logic" in SQL*Plus. The if script accepts as input a condition (whatever condition) to be evaluated. The script evaluates the condition and if the condition is TRUE, then it prints the beginning of a comment "/*". This output in combination with the line "-- end if */" in the parent script, results in the commenting out of a whole script section (the one that must not run based on the condition). If the condition evaluates to FALSE, then the if script prints nothing and thus the following commands are executed normally. Thus, we have implemented "conditional execution logic" in our SQL*Plus script.

In the following listing we show the magical if.sql script. Note that this script is not to be used as a stand alone script but it must be invoked from within another SQL*Plus script. Moreover, we have to always remember to include the closing end of a comment "-- end if*/"  at the end of this custom "if block".

------------------ --  if.sql --    DESCRIPTION --      Script to achieve conditional execution within an sqlplus script.  --      It is meant to be used within another script and NOT as a stand  --      alone script. --    ***NOTE*** -- Whenever you call if.sql in a script you have to remember to  --      include at the end, the closing end of the comment (*/). ------------------ col do_next new_val do_next noprint; select       case          when &1 then 'null'          else 'comment_on'       end as do_next from dual; @&do_next

The if script all it does is to evaluate the input condition (which is passed as an input parameter) and if the condition is true then the "null.sql" script is called, or if the condition is false, then the "comment_on.sql" script is called. The "null.sql" script is just an empty file. The "comment_on.sql" script just prints the beginning of a comment "/*".

-- comment_on.sql --      Description: just print the beginning of a comment --         (only to be used within another SQL*Plus script and not as stand alone script) /*

Lets try out the script to see if it works:

nkarag@DWHPRD> @branching
Give me an answer yes or no (y/n)?y

'YOUHAVESAIDYES'
-----------------
you have said yes

nkarag@DWHPRD> @branching
Give me an answer yes or no (y/n)?n

'YOUHAVESAIDNO'
----------------
you have said no
 

Listing 2: Test script to demonstrate conditional execution in SQL*Plus.

4. Summary

SQL*Plus scripting is the coding bread and butter of Oracle database professionals. Very often there is a need for some conditional execution logic in our script. This boils down to implementing an IF-construct with SQL*Plus commands, since there is not such thing as an SQL*Plus IF-statement. Moreover, when our script includes some PL/SQL blocks, then there might be a need for a conditional compilation of this code, which will take place dynamically when the script is invoked based on some condition.
In this post, we have shown how both of these needs can be easily implemented with the trick of dynamically commenting out different parts of the code. Hope that it is useful.