Re: Conditional logic in a main SQLPLUS script file
- From: Kjetil Strønen <kjetil@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 05 Jan 2011 12:48:44 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
