Oracle | DBMS_DB_VERSION and PLSQL

Oracle DBMS_DB_VERSION Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions.

AUTHID N/A

Constants

Name	Data Type	Value
RELEASE	PLS_INTEGER	0
VERSION	PLS_INTEGER	&C_ORACLE_HIGH_VERSION_0_DOTS
VER_LE_9	BOOLEAN	FALSE
VER_LE_9_1	BOOLEAN	FALSE
VER_LE_9_2	BOOLEAN	FALSE
VER_LE_10	BOOLEAN	FALSE
VER_LE_10_1	BOOLEAN	FALSE
VER_LE_10_2	BOOLEAN	FALSE
VER_LE_11	BOOLEAN	FALSE
VER_LE_11_1	BOOLEAN	FALSE
VER_LE_11_2	BOOLEAN	FALSE
VER_LE_12	BOOLEAN	FALSE
VER_LE_12_1	BOOLEAN	FALSE
VER_LE_12_2	BOOLEAN	FALSE
VER_LE_18	BOOLEAN	FALSE
VER_LE_19	BOOLEAN	FALSE
VER_LE_20	BOOLEAN	FALSE
VER_LE_21	BOOLEAN	TRUE

Dependencies

DBMS_DBFS_CONTENT	DBMS_DBFS_SFS	DBMS_FUSE
DBMS_DBFS_CONTENT_ADMIN	DBMS_DBFS_SFS_ADMIN

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-39962	invalid parameter for PLSQL_CCFLAGS

First Available 10.2

Initialization Parameter PLSQL_CCFLAGS

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsdbvn.sql

Subprograms

PLSQL_CCFLAGS

DBMS_DB_VERSION Demos

PLSQL_CCFLAGS

Values

<vi> has the form of an unquoted PL/SQL identifier. It is unrestricted and can be a reserved word or a keyword. The text is insensitive to case. Each one is known as a flag or flag name. Each <vi> can occur more than once in the string, each occurrence can have a different flag value, and the flag values can be of different kinds.
<ci> is one of the following: a PL/SQL boolean literal, a PLS_INTEGER literal, or the literal NULL. The text is insensitive to case. Each one is known as a flag value and corresponds to a flag name.

You can define any allowable value for PLSQL_CCFLAGS. However, Oracle recommends that this parameter be used for controlling the conditional compilation of debugging or tracing code. It is recommended that the following identifiers not be used as flag name values:

Names of Oracle parameters (for example, NLS_LENGTH_SEMANTICS)
Identifiers with any of the following prefixes: PLS_, PLSQL_, PLSCC_, ORA_, ORACLE_, DBMS_, SYS_

Parameter type String

Syntax PLSQL_CCFLAGS = '<v1>:<c1>,<v2>:<c2>,...,<vn>:<cn>'

Default value Empty string ('')

Modifiable ALTER SESSION, ALTER SYSTEM

Range of values Any string literal that satisfies the internal syntax

Examples ALTER SESSION SET PLSQL_CCFLAGS = 'DeBug:TruE'; ALTER SESSION SET PLSQL_CCFLAGS = 'debug:TRUE'; plsql_ccflags = 'ACTIVE_STATUS_ONLY:TRUE'; plsql_ccflags = FOREIGN_ACCOUNTS:TRUE';

DBMS_DB_VERSION Demos

Variable Definition

set serveroutput on

    

      CREATE OR REPLACE PROCEDURE whetstone AUTHID DEFINER IS

      

      -- Notice that conditional compilation constructs can interrupt a regular PL/SQL statement.

      -- You can locate a conditional compilation directive anywhere there is whitespace in the regular statement.

      

      SUBTYPE my_real IS

      $IF  DBMS_DB_VERSION.VER_LE_11 $THEN

        NUMBER

      $ELSE

        BINARY_DOUBLE

      $END;

      

      t CONSTANT my_real := $IF  DBMS_DB_VERSION.VER_LE_11 $THEN 0.499975

      $ELSE 0.499975d

      $END;

      

      t2 CONSTANT my_real := $if  DBMS_DB_VERSION.VER_LE_11 $THEN

        2.0

      $ELSE

        2.0d

      $END;

      

      x CONSTANT my_real := $IF  DBMS_DB_VERSION.VER_LE_11 $THEN

        1.0

      $ELSE

        1.0d

      $END;

      

      y CONSTANT my_real := $IF  DBMS_DB_VERSION.VER_LE_11 $THEN 1.0

      $ELSE 1.0d

      $END;

      

      z MY_REAL;

      

      
        
          -- procedure nested inside whetstone

            PROCEDURE P(x my_real, y my_real, z OUT NOCOPY my_real) AUTHID DEFINER IS

             x1 my_real;

             y1 my_real;

            BEGIN

              x1 := x;

              y1 := y;

              x1 := t * (x1 + y1);

              y1 := t * (x1 + y1);

              z := (x1 + y1)/t2;

            END P;
        
      


      BEGIN

        P(x, y, z);

        dbms_output.put_line('z = '|| z);

      END whetstone;

      /

PL/SQL Code set serveroutput on BEGIN $IF dbms_db_version.ver_le_10 $THEN dbms_output.put_line('version 10g or earlier code'); $ELSIF dbms_db_version.ver_le_11 $THEN dbms_output.put_line('version 11g code'); $ELSE dbms_output.put_line('version 12c code'); $ELSE dbms_output.put_line('version 18c or later code'); $END -- note that there is no semi-colon END; /

PL/SQL Code


      
        
          CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR2 AUTHID DEFINER IS

            BEGIN

              $IF $$ppval $THEN

                RETURN 'PPVAL was TRUE';

              $ELSE

                RETURN 'PPVAL was FALSE';

              $END -- note that there is no semi-colon

            END myfunc;

            /
        
      
      

      SELECT myfunc FROM dual;

      

      ALTER SESSION SET plsql_ccflags = 'PPVAL:TRUE';

      

      SELECT myfunc FROM dual;

      

      ALTER FUNCTION myfunc COMPILE;

      

      ALTER SESSION SET plsql_ccflags = 'PPVAL:FALSE';

      

      SELECT myfunc FROM dual;

      

      ALTER FUNCTION myfunc COMPILE

      plsql_ccflags = 'PPVAL:TRUE'

      REUSE SETTING;

Debug Demo set serveroutput on CREATE OR REPLACE PACKAGE debug_pkg AUTHID DEFINER IS debug_flag CONSTANT BOOLEAN := FALSE; END; / CREATE OR REPLACE PROCEDURE myproc IS BEGIN $IF debug_pkg.debug_flag $THEN dbms_output.put_line('Debug=T'); $ELSE dbms_output.put_line('Debug=F'); $END -- note that there is no semi-colon END myproc; / exec myproc