Re: Conditional logic in a main SQLPLUS script file

From: Kjetil Strønen <kjetil_at_oneteam.no>
Date: Wed, 05 Jan 2011 12:48:44 +0100
Message-ID: <1294228124.2514.27.camel_at_ks-lap-02.oneteam.no>

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:
>
>
> _at_script_0.sql
> $IF packageA.option_1 = 1 $THEN
> _at_script_1.sql
> $ELSE
> _at_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; _at_&script_name
select decode(2,1,'script1.sql',2,'script2.sql') some_column from dual; _at_&script_name
select decode(3,1,'script1.sql',2,'script2.sql') some_column from dual; _at_&script_name

---

script1.sql:

---

select 'This is script1' from dual;

---

script2.sql:

---

select 'This is script2' from dual;

---

##########

SQL> _at_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> _at_&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> _at_&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> _at_&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

Received on Wed Jan 05 2011 - 05:48:44 CST