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.

Answer 1

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>