Re: Conditional logic in a main SQLPLUS script file

On Wed, 2011-01-05 at 11:12 +0000, Dylan Farre wrote:
> I am looking for a way to implement conditional logic in a main sql
> script file, that calls other script files.
> 
<snip>
> 
> But what I want to do is something like this:
> 
> 
> @script_0.sql
>  $IF packageA.option_1 = 1 $THEN
>     @script_1.sql
>  $ELSE
>     @script_2.sql
>  $END
> ...
> 
> 
> I understand that this does not work, but is there a another way to
> achieve what I am trying to do here? 

Would something like this work?:

wrapper.sql:
---------
col some_column new_value script_name
select decode(1,1,'script1.sql',2,'script2.sql') some_column from dual;
@&script_name
select decode(2,1,'script1.sql',2,'script2.sql') some_column from dual;
@&script_name
select decode(3,1,'script1.sql',2,'script2.sql') some_column from dual;
@&script_name
---------

script1.sql:
---------
select 'This is script1' from dual;
---------

script2.sql:
---------
select 'This is script2' from dual;
---------

##########
SQL> @wrapper
SQL> col some_column new_value script_name
SQL> select decode(1,1,'script1.sql',2,'script2.sql') some_column from
dual;

SOME_COLUMN
-----------
script1.sql

SQL> @&script_name
SQL> select 'This is script1' from dual;

'THISISSCRIPT1'
---------------
This is script1

SQL> select decode(2,1,'script1.sql',2,'script2.sql') some_column from
dual;

SOME_COLUMN
-----------
script2.sql

SQL> @&script_name
SQL> select 'This is script2' from dual;

'THISISSCRIPT2'
---------------
This is script2

SQL> select decode(3,1,'script1.sql',2,'script2.sql') some_column from
dual;

S
-


SQL> @&script_name
SQL> 
########

Obviously; replace the first argument to decode with
"packageA.option_1".

An unexpected value from "packageA.option_1" would (as the last
execution shows) result in a null-value for script_name, thus no script
being called, but you could add a default value (i.e "else-script") in
the decode for that.

--Kjetil

--
http://www.freelists.org/webpage/oracle-l


Other related posts: