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.
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.
