CODEWITHSUNDEEP
oracle-databaseplsql
Dinidu Hewage
Dinidu Hewage

Reputation: 2191

PL/SQL Conditional Compilation with Conditional Operators

can we use conditional operators like "AND" , "OR" in ORACLE conditional compilation?

$IF $$con1 AND $$con2 $THEN 

$END

Upvotes: 1

Views: 2956

Answers (4)

APC
APC

Reputation: 146179

Here's a simple procedure which uses conditional compilation:

create or replace procedure cc_test is
    n simple_integer := 10;
begin
    $if $$tst_flag = 1 $then
        n := n + 10;
    $elsif $$tst_flag = 2 $then
        n := n + 15;
    $elsif $$tst_flag = 3 $then
        n := n - 5;
    $else   
        n := 42;
    $end
    dbms_output.put_line ('n = ' || to_char(n));
end;
/

Its default behaviour is this:

...
Procedure created.

SQL> exec cc_test
n = 42

PL/SQL procedure successfully completed.

SQL>

We can vary the behaviour by setting conditional compilation flags and re-compiling the procedure .

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'tst_flag:1';

Session altered.

SQL> exec cc_test   
n = 42

PL/SQL procedure successfully completed.

SQL> alter procedure cc_test compile;

Procedure altered.

SQL> exec cc_test
n = 20

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'tst_flag:3';

Session altered.

SQL>  alter procedure cc_test compile;

Procedure altered.

SQL>  exec cc_test
n = 5

PL/SQL procedure successfully completed.

SQL>

So, the question is, can we combine the effect of multiple flags, like this?

create or replace procedure cc_test_2 is
    n simple_integer := 10;
begin
    $if $$tst_flag_1 = 1 and $$tst_flag_2 = 1 $then
        n := 12;
    $elsif $$tst_flag_1 = 2 and $$tst_flag_2 = 3 $then
        n := 23;
    $else   
        n := 42;
    $end
    dbms_output.put_line ('n = ' || to_char(n));
end;
/

Yes we can:

SQL> exec cc_test_2
n = 42

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'tst_flag_1:2, tst_flag_2:3';

Session altered.

SQL> alter procedure cc_test_2 compile;

Procedure altered.

SQL> exec cc_test_2
n = 23

PL/SQL procedure successfully completed.

SQL>

This behaviour is limited to the session. Make it permanent by setting the flags at compilation time:

SQL> alter procedure cc_test_2 compile  PLSQL_CCFLAGS = 'tst_flag_1:2, tst_flag_2:3';

Procedure altered.

SQL>  exec cc_test_2
n = 23

PL/SQL procedure successfully completed.

SQL> conn apc
Enter password: 
Connected.
SQL> set serveroutput on
SQL> exec cc_test_2
n = 23

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Ravi
Ravi

Reputation: 31397

According to Oracle Doc

Using conditional compilation, you can customize the functionality in a PL/SQL application without having to remove any source code

$IF boolean_static_expression $THEN text
  [ $ELSIF boolean_static_expression $THEN text ]
  [ $ELSE text ]
$END

And, BOOLEAN static expressions include:

TRUE, FALSE, and the literal NULL

x > y, x < y, x >= y, x <= y, x = y, and x <> y where x and y are PLS_INTEGER static expressions

NOT x, x AND y, x OR y, x > y, x >= y, x = y, x <= y, x <> y where x and y are BOOLEAN static expressions

x IS NULL and x IS NOT NULL where x is a static expression

So, answer is YES

Upvotes: 1

Sudz
Sudz

Reputation: 4308

Yes, you can try below code

declare

 $IF 1 = 1 AND  1= 1 $THEN
   cursor c1 is
   select 'A' from dual;
 $ELSE
   cursor c2 is
   select 'B' from dual;
 $END

begin

 dbms_output.put_line('Working');
 open c2;
 close c2;

end;

Upvotes: 2

Dinidu Hewage
Dinidu Hewage

Reputation: 2191

Yes conditional operators are allowed to use in conditional compilation.

Upvotes: 0

Related Questions