Oracle PL/SQL Conditional Declaration?

Question

Can we do the conditional declarations of variables in pl/sql functions or procedures? For example can we do something like below:

CREATE OR REPLACE FUNCTION get_total_sales(
    in_year PLS_INTEGER
) 
RETURN NUMBER
IS
    IF condtion THEN
       variable_name_1 BINARY_FLOAT;
    ELSE
       variable_name_2 NUMBER;
    END;

BEGIN
   --- Function Logic
END;

If the condition is met, I need to declare a variable called variable_name_1 and if not I don't want to declare it and instead need to declare a variable called variable_name_2.

When is condition evaluated - at compile time, based on environment (e.g. dev/prod, customer licence) or are you trying to change this at run time for each call? — Alex Poole
– Alex Poole, Commented Jun 26, 2020 at 6:38

pifor · Accepted Answer · 2020-06-26 06:32:32Z

8

There is a possibilty since Oracle 10.

Example:

SQL> CREATE OR REPLACE PROCEDURE test AS
  2    $IF $$test_on $THEN
  3      v1 BINARY_FLOAT;
  4    $ELSE
  5     v2 NUMBER;
  6    $END
  7    v boolean;
  8  BEGIN
  9   v := true;
 10  END test;
 11  /

Procedure created.

SQL> show errors
No errors.
SQL> --
SQL> ALTER PROCEDURE test COMPILE PLSQL_CCFLAGS = 'test_on:TRUE' REUSE SETTINGS;

Procedure altered.

SQL> --
SQL> BEGIN
  2  
  3    DBMS_PREPROCESSOR.print_post_processed_source (
  4      object_type => 'PROCEDURE',
  5      schema_name => 'SYSTEM',
  6      object_name => 'TEST');
  7  END;
  8  /
PROCEDURE test AS
v1 BINARY_FLOAT;
v boolean;
BEGIN
v := true;
END test;

PL/SQL procedure successfully completed.

SQL> show errors
No errors.
SQL> --
SQL> ALTER PROCEDURE test COMPILE PLSQL_CCFLAGS = 'test_on:FALSE' REUSE SETTINGS;

Procedure altered.

SQL> --
SQL> BEGIN
  2  
  3    DBMS_PREPROCESSOR.print_post_processed_source (
  4      object_type => 'PROCEDURE',
  5      schema_name => 'SYSTEM',
  6      object_name => 'TEST');
  7  END;
  8  /
PROCEDURE test AS
v2 NUMBER;
v boolean;
BEGIN
v := true;
END test;

PL/SQL procedure successfully completed.

SQL>

answered Jun 26, 2020 at 6:32

pifor

7,9722 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Littlefoot Over a year ago

Whoa! Although 10g is an old piece of software, I've never seen that (obviously, never used it either). Cool!

TrojanName Over a year ago

You can do a lot actually. Steven Feuerstein did an excellent writeup stevenfeuersteinonplsql.blogspot.com/2019/03/…

Collectives™ on Stack Overflow

Oracle PL/SQL Conditional Declaration?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related