You Asked

Hi,
We run the same script package to each environment (production, acceptance test, etc.). We have some of these scripts which shouldn't run e.g. into PRO db. Is there any way to define which script can run in which environment if environment type is asked from the runner user as input?
Thx,
Miklos

and Connor said...

If you want to abandon the script entirely, you can do something like:

whenever sqlerror exit
select 1 / case when '&1' = 'PROD' then 0 else 1 end from dual;
whenever sqlerror continue

or if you want nice message

whenever sqlerror exit
begin
  if '&1' = 'PROD' then
    raise_application_error(-20000,'Not running in this PROD') ;
  end if;
end;
/
whenever sqlerror continue

For portions of scripts, you can wrap the content in a PLSQL block and then dynamically set a comment flag, eg

define _ENV="--"
col env noprint new_value _ENV
select
    case when '&1' = 'PROD' then '--' else '' end env
from
    dual;

begin
  null;
&_ENV  execute immediate 'drop table t1';
&_ENV  delete from t;
end;
/


update t set object_id = 0 where rownum <= 10;
commit;

Running that in "PROD" means the two commands are commented out, where in "DEV" they would be run.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Conditional Compilation in PL/SQL

Rajeshwaran, Jeyabal, February 17, 2017 - 2:18 pm UTC

....
We run the same script package to each environment (production, acceptance test, etc.). We have some of these scripts which shouldn't run e.g. into PRO db.
....

This remains me about the Conditional Compilation feature of Pl/SQL introduced in Oracle 10g database.

demo@ORA12C> create or replace procedure process_data
  2  as
  3  $if $$debug $then
  4     l_cpu int;
  5     l_time int;
  6  $end
  7     l_cnt int;
  8  begin
  9     $if $$debug $then
 10             l_cpu := dbms_utility.get_cpu_time;
 11             l_time := dbms_utility.get_time;
 12     $end
 13     select count(*) into l_cnt
 14     from all_users, all_objects;
 15     $if $$debug $then
 16             dbms_output.put_line(
 17                     'Cpu time ='|| ( dbms_utility.get_cpu_time - l_cpu )||
 18                     ' Elapsed time ='|| ( dbms_utility.get_time - l_time ) );
 19     $end
 20  end;
 21  /

Procedure created.

demo@ORA12C>
demo@ORA12C> alter procedure process_data compile
  2  plsql_ccflags='debug:true';

Procedure altered.

demo@ORA12C> exec process_data;
Cpu time =301 Elapsed time =306

PL/SQL procedure successfully completed.

demo@ORA12C> alter procedure process_data compile
  2  plsql_ccflags='debug:false';

Procedure altered.

demo@ORA12C> exec process_data;

PL/SQL procedure successfully completed.

demo@ORA12C>

we use this technique to add the debug info inside packages and procedures and turn then "OFF" in Production and "ON" in lower environments (like Pre-prod environment for bench-marking)

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Conditional Compilation in PL/SQL

More to Explore

PL/SQL demos

PL/SQL docs

Skip scripts based on a variable value during run

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Conditional Compilation in PL/SQL

More to Explore

PL/SQL demos

PL/SQL docs