RE: Conditional logic in a main SQLPLUS script file

From: <>
Date: Wed, 5 Jan 2011 09:03:53 -0500
Message-ID: <>

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';


drop version_script; -- no longer needed  



[] On Behalf Of Stephane Faroult Sent: Wednesday, January 05, 2011 6:23 AM To:
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

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

     dbms_output.put_line('_at_script_2.sql');   end if;

spool off

HTH Stephane Faroult
RoughSea Ltd <>
Konagora <>
RoughSea Channel on Youtube <>

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:  


BEGIN     $IF packageA.option_1 = 1 $THEN


  $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:  


 $IF packageA.option_1 = 1 $THEN


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

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

Original text of this message