CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_DB_VERSION and PLSQL_CCFLAGS
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsdbvn.sql

Constants
Name Data Type Value
RELEASE PLS_INTEGER 1
VERSION PLS_INTEGER 11
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 TRUE
VER_LE_11_1 BOOLEAN TRUE
Dependencies DBMS_PREPROCESSOR
Error Messages
Error Code Reason
ORA-39962 invalid parameter for PLSQL_CCFLAGS
Initialization Parameter PLSQL_CCFLAGS
Security Model Execute is granted to PUBLIC
 
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 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_9 $THEN 
  NUMBER
$ELSE
  BINARY_DOUBLE
$END;  -- note that there is no semi-colon

t CONSTANT my_real := $IF DBMS_DB_VERSION.VER_LE_9 $THEN 0.499975 
$ELSE 0.499975d 
$END;

t2 CONSTANT my_real := $if DBMS_DB_VERSION.VER_LE_9 $THEN
  2.0
$ELSE 
  2.0d
$END;

x CONSTANT my_real := $IF DBMS_DB_VERSION.VER_LE_9 $THEN
  1.0
$ELSE
  1.0d
$END;

y CONSTANT my_real := $IF DBMS_DB_VERSION.VER_LE_9 $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) 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_outputversion 10 and earlier code
  $ELSIF dbms_db_version.ver_le_11 $THEN
    version 11 code
  $ELSE
    version 12 and later code
  $END  -- note that there is no semi-colon
END;
/

PL/SQL Code
CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR2 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 SETTINGS;

Debug Demo
set serveroutput on
CREATE OR REPLACE PACKAGE debug_pkg 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

 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [177 users online]    © 2010 psoug.org