Oracle DBMS_DB_VERSION
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 1
VERSION PLS_INTEGER 12
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 TRUE
VER_LE_12_1 BOOLEAN TRUE
Dependencies
DBMS_DBFS_CONTENT DBMS_FUSE WWV_FLOW_ESCAPE
DBMS_DBFS_CONTENT_ADMIN WWV_DBMS_SQL WWV_FLOW_GLOBAL
DBMS_DBFS_SFS WWV_FLOW WWV_FLOW_SESSION_STATE
DBMS_DBFS_SFS_ADMIN WWV_FLOW_DEBUG WWV_FLOW_UTILITIES
Documented Yes
Exceptions
Error Code Reason
ORA-39962 invalid parameter for PLSQL_CCFLAGS
First Available 10.2.0.1
Initialization Parameter PLSQL_CCFLAGS
Security Model Owned by SYS with EXECUTE granted to PUBLIC and the APEX_040200 role
Source {ORACLE_HOME}/rdbms/admin/dbmsdbvn.sql
Subprograms
 
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 10 and earlier code');
  $ELSIF dbms_db_version.ver_le_11 $THEN
    dbms_output.put_line('version 11 code');
  $ELSE
    dbms_output.put_line('version 12 and 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

Related Topics
PLSQL_CCFLAGS
Packages
Predefined Inquiry Directives
UTL_IDENT

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved