FAQ
I am looking for a way to implement conditional logic in a main sql script
file, that calls other script files.

In PLSQL, this is easy using regular IF THEN ELSE statements. It is even
easy to add or remove blocks of PLSQL from compiled objects (using
pre-compiler directives). For example, in a stored procedure, by including
something like this:

CREATE OR REPLACE PROCEDURE ZZZ AS

..

BEGIN

$IF packageA.option_1 = 1 $THEN
do_something();
$ELSE

do_something_else();
$END

END;

/

Which will create two different versions of procedure ZZZ, depending on the
value of packageA.option_1. Sometimes this is handy, if most of the code
in procedure ZZZ is the same and there is only a small difference required
under certain circumstances.

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?

Any suggestions? Thanks.

Search Discussions

  • Niall Litchfield at Jan 5, 2011 at 11:17 am
    Shell scripting? Perl?
    On Wed, Jan 5, 2011 at 11:12 AM, Dylan Farre wrote:

    I am looking for a way to implement conditional logic in a main sql script
    file, that calls other script files.

    In PLSQL, this is easy using regular IF THEN ELSE statements. It is even
    easy to add or remove blocks of PLSQL from compiled objects (using
    pre-compiler directives). For example, in a stored procedure, by including
    something like this:

    CREATE OR REPLACE PROCEDURE ZZZ AS
    ..

    BEGIN
    $IF packageA.option_1 = 1 $THEN
    do_something();
    $ELSE
    do_something_else();
    $END
    END;
    /

    Which will create two different versions of procedure ZZZ, depending on the
    value of packageA.option_1. Sometimes this is handy, if most of the code
    in procedure ZZZ is the same and there is only a small difference required
    under certain circumstances.

    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?

    Any suggestions? Thanks.
    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark Brinsmead at Jan 9, 2011 at 3:20 am
    /bin/cpp, maybe? The C pre-processor. You can also use #include and any
    other preprocessor directive. You are not limited to #ifdef
    On Wed, Jan 5, 2011 at 6:17 AM, Niall Litchfield wrote:

    Shell scripting? Perl?
    On Wed, Jan 5, 2011 at 11:12 AM, Dylan Farre wrote:

    I am looking for a way to implement conditional logic in a main sql script
    file, that calls other script files.

    In PLSQL, this is easy using regular IF THEN ELSE statements. It is even
    easy to add or remove blocks of PLSQL from compiled objects (using
    pre-compiler directives). For example, in a stored procedure, by including
    something like this:

    CREATE OR REPLACE PROCEDURE ZZZ AS
    ..

    BEGIN
    $IF packageA.option_1 = 1 $THEN
    do_something();
    $ELSE
    do_something_else();
    $END
    END;
    /

    Which will create two different versions of procedure ZZZ, depending on
    the value of packageA.option_1. Sometimes this is handy, if most of the code
    in procedure ZZZ is the same and there is only a small difference required
    under certain circumstances.

    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?

    Any suggestions? Thanks.


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info
    --
    Cheers,
    -- Mark Brinsmead
    Senior DBA,
    The Pythian Group
    http://www.pythian.com/blogs

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephane Faroult at Jan 5, 2011 at 11:23 am
    The simples thing that comes to my mind is to add one script layer -
    generate your script from a script that includes some PL/SQL

    Something such as

    set feedback off
    set trimspool on
    set recsep off
    set serveroutput on
    spool main_script.sql
    begin

    dbms_output.put_line('_at_scrip_0.sql');
    if packageA.option_1 = 1
    then
    dbms_output.put_line('_at_script_1.sql');
    else

    dbms_output.put_line('_at_script_2.sql');
    end if;
    end;
    /
    spool off
    _at_main_script.sql

    HTH

    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
    On 01/05/2011 12:12 PM, Dylan Farre wrote:
    I am looking for a way to implement conditional logic in a main sql
    script file, that calls other script files.

    In PLSQL, this is easy using regular IF THEN ELSE statements. It is
    even easy to add or remove blocks of PLSQL from compiled objects
    (using pre-compiler directives). For example, in a stored procedure,
    by including something like this:

    CREATE OR REPLACE PROCEDURE ZZZ AS
    ..

    BEGIN
    $IF packageA.option_1 = 1 $THEN
    do_something();
    $ELSE
    do_something_else();
    $END
    END;
    /

    Which will create two different versions of procedure ZZZ, depending
    on the value of packageA.option_1. Sometimes this is handy, if most of
    the code in procedure ZZZ is the same and there is only a small
    difference required under certain circumstances.

    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?

    Any suggestions? Thanks.
    --
    http://www.freelists.org/webpage/oracle-l
  • Rajendra.pande_at_ubs.com at Jan 5, 2011 at 2:03 pm
    Or a variation of this that I have seen in a lot of scripts from oracle

    Snippet from catupend.sql



    VARIABLE utl_name VARCHAR2(50)

    COLUMN :utl_name NEW_VALUE utl_file NOPRINT;



    BEGIN

    IF '&&mig_file' = '1102000' THEN

    :utl_name := 'catupshd.sql';

    ELSE

    :utl_name := 'utlmmig.sql';

    END IF;

    END;

    /

    drop version_script; -- no longer needed



    SELECT :utl_name FROM DUAL;

    _at__at_&utl_file



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stephane Faroult
    Sent: Wednesday, January 05, 2011 6:23 AM
    To: dylan.farre_at_gmail.com
    Cc: oracle-l@freelists.org
    Subject: Re: Conditional logic in a main SQLPLUS script file



    The simples thing that comes to my mind is to add one script layer -
    generate your script from a script that includes some PL/SQL

    Something such as

    set feedback off
    set trimspool on
    set recsep off
    set serveroutput on
    spool main_script.sql
    begin

    dbms_output.put_line('_at_scrip_0.sql');
    if packageA.option_1 = 1
    then
    dbms_output.put_line('_at_script_1.sql');
    else

    dbms_output.put_line('_at_script_2.sql');
    end if;
    end;
    /
    spool off
    _at_main_script.sql

    HTH

    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 01/05/2011 12:12 PM, Dylan Farre wrote:

    I am looking for a way to implement conditional logic in a main sql
    script file, that calls other script files.



    In PLSQL, this is easy using regular IF THEN ELSE statements. It is even
    easy to add or remove blocks of PLSQL from compiled objects (using
    pre-compiler directives). For example, in a stored procedure, by
    including something like this:



    CREATE OR REPLACE PROCEDURE ZZZ AS

    ..



    BEGIN



    $IF packageA.option_1 = 1 $THEN

    do_something();

    $ELSE

    do_something_else();

    $END



    END;

    /



    Which will create two different versions of procedure ZZZ, depending on
    the value of packageA.option_1. Sometimes this is handy, if most of the
    code in procedure ZZZ is the same and there is only a small difference
    required under certain circumstances.



    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?



    Any suggestions? Thanks.

    Please visit our website at
    http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
    for important disclosures and information about our e-mail
    policies. For your protection, please do not transmit orders
    or instructions by e-mail or include account numbers, Social
    Security numbers, credit card numbers, passwords, or other
    personal information.
  • Kjetil Strønen at Jan 5, 2011 at 11:48 am

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 5, '11 at 11:12a
activeJan 9, '11 at 3:20a
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase