RE: Conditional logic in a main SQLPLUS script file

From: <rajendra.pande_at_ubs.com>
Date: Wed, 5 Jan 2011 09:03:53 -0500
Message-ID: <D4C8B99EB96F2C42B4E19A3B87664F5EB36B25_at_NSTMC612PEX.ubsamericas.net>

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
[mailto: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_at_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.

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

Received on Wed Jan 05 2011 - 08:03:53 CST