C H A P T E R 4
Warnings and Conditional Compilation
When Oracle implemented Java in the RDBMS kernel, it was thought to be the end of PL/SQL. However, in version 10.2, Oracle rewrote the PL/SQL compiler and implemented various new features, proving that there is a future for PL/SQL. This chapter will cover two features that were first seen in Oracle 10.2: PL/SQL warnings and PL/SQL conditional compilation.
PL/SQL Warnings
In Oracle 10.2, Oracle implemented PL/SQL warnings. The feature gives the ability to compile PL/SQL and get warnings if some of the code implements poor practices or conflicts with reserved words or Oracle functions. Prior to Oracle 10.2, your PL/SQL would sometimes compile—only to throw errors at runtime. The purpose of PL/SQL warnings is to warn of potential mistakes at compile time when they are easier to fix, thereby ensuring that code being applied to the database is as robust and optimal as possible.
Basics
Before you start looking at what you gain from the PL/SQL warnings, first look at some basic aspects of the feature. By default, PL/SQL warnings are disabled. PL/SQL warnings can be enabled at the system, session, or procedure level. PL/SQL warnings are divided into three categories:
Severe: These are warnings about code that contains a conflict with functions in SYS.STANDARD or SQL functions.
Performance: These are warnings about code that may have a performance impact, such as the use of wrong data types in queries and the lack of use of NOCOPY compile directive when appropriate.
Informational: These are warnings relating to code that does no harm and/or may even be removed.
When you create or compile a stored procedure, it inherits the settings in the session you are running by default. The PL/SQL warning level is determined by the value of the parameter plsql_warnings. You can show the current parameter value as follows:
SQL@V112> show parameter plsql_warnings
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings string DISABLE:ALL
The default value is DISABLE:ALL
If you are not allowed to execute a SHOW PARAMETER command, the value of plsql_warnings can be examined with the following query:
SQL@V112> SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;
GET_WARNING_SETTING_STRING
----------------------------------------------------------------
DISABLE:ALL
To enable all warnings for the current session, execute the following:
SQL@V112> alter session set plsql_warnings='ENABLE:ALL';
Session altered.
Note In most cases, you need the ALTER SESSION privilege to alter your session parameters. This is not the case when it comes to altering the plsql_warnings parameter.
Furthermore, the plsql_warnings string is not case sensitive.
To enable severe and performance warnings and disable informational warnings in one go, execute the following statement:
SQL@V112> alter session set plsql_warnings='ENABLE:SEVERE,ENABLE:PERFORMANCE,
DISABLE:INFORMATIONAL';
Session altered.
The parameter setting is not cumulative. If you first set plsql_warnings to ENABLE:SEVERE and afterwards ENABLE:PERFORMANCE, then only warnings regarding performance is enabled.
Should you want to set the parameter from within PL/SQL, you can do so by calling DBMS_WARNINGS.SETWARNING_SETTING_STRING. Enabling only setting one will disable others. This example will enable severe warnings only and at the session level:
SQL@V112> EXEC DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:SEVERE', 'SESSION'),
PL/SQL procedure successfully completed.
Enabling warnings does not affect execution time, but it may affect compilation time a bit. Should you compile a procedure 1,000 times, you'd find the difference between compiling with warnings and compiling without warnings will be about one second in favor of the one not having warnings enabled.
If a procedure is compiled and compilation warnings occur, the procedure can still be executed. The warnings are there to give you a heads-up that you might have a problem in your code. But the compiler doesn't know everything. Not all warnings are problems. You are always free to execute your code.
Tip If you want to examine what type of warnings you can get and you have access to the Oracle binaries on the database server, you can examine the file $ORACLE_HOME/plsql/mesg/plw<LANG>.msg. This file contains the all the warning messages.
Using Warnings
Let's have a look at some examples. First, create a simple table called T1 with a column named KEY of type VARCHAR2 and a column called VALUE of VARCHAR2. Assume that the column KEY, although defined as VARCHAR2, only contains numbers. Sadly, you will sometimes see this approach in real-world production systems. Here is the code to create the table:
SQL@V112> CREATE TABLE T1 (
2 KEY VARCHAR2(10) CONSTRAINT PK_T1 PRIMARY KEY,
3 VALUE VARCHAR2(10))
4 /
Table Created.
Now, load the table with test data.
SQL@V112> insert into t1 select rownum, substr(text,1,10) from all_source where rownum < 10000;
9999 rows created.
Now enable severe and performance PL/SQL warnings.
SQL@V112> alter session set plsql_warnings='ENABLE:SEVERE, ENABLE:PERFORMANCE';
Session altered.
Next, create a function that will return the VALUE for a given key.
SQL@V112> CREATE OR REPLACE FUNCTION GET_T1_KEY (P_KEY NUMBER) RETURN VARCHAR2 IS
2 l_value T1.VALUE%TYPE;
3 BEGIN
4 SELECT VALUE INTO l_value FROM T1 WHERE KEY = P_KEY;
5 RETURN l_value;
6 EXCEPTIONS
7 WHEN NO_ROWS_FOUND THEN
8 RETURN 'No value found';
9 END;
10 /
SP2-0804 Procedure created with compilation warnings.
The procedure compiles with warnings. To show the warnings in SQL*Plus, simply do a show error, like so:
SQL@V112> show err
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05018: unit GET_T1_KEY omitted optional AUTHID clause;
default value DEFINER used
4/45 PLW-07204: conversion away from column type may result in
sub-optimal query plan
Note If you examine ATTRIBUTE and MESSAGE_NUMBER in the USER_ERRORS table, you'll see that ATTRIBUTE holds either ERROR or WARNING and the error/warning number is in MESSAGE_NUMBER column.
Even though the function is compiled with warnings, you will still be able to execute it.
SQL@V112> var txt varchar2(10);
SQL@V112> exec :txt := GET_T1_KEY(1);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
Whether or not the KEY column should or should not be changed to NUMBER is not the question for now. In this case, you need to change the function. So change the P_KEY parameter to VARCHAR2 and get rid of warning PLW-05018 by adding AUTHID DEFINER to the function. Here's the new version of the function:
SQL@V112> CREATE OR REPLACE FUNCTION GET_T1_KEY (P_KEY VARCHAR2) RETURN VARCHAR2
2 AUTHID DEFINER
3 IS
4 L_value T1.VALUE%TYPE;
5 BEGIN
6 SELECT VALUE INTO l_value FROM T1 WHERE KEY = P_KEY;
7 RETURN l_value;
8 EXCEPTIONS
9 WHEN NO_ROWS_FOUND THEN
10 RETURN 'No value found';
11 END;
12 /
Function created
This new version of the function is created without errors or warnings. Notice that it executes faster (twice as fast, in this case) now that the correct data type is being used and the function uses the index to look up the key. Here's an example execution:
SQL@V112> exec :txt := GET_T1_KEY('1'),
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
SQL@V112>
The PLW-7204 warning tells you that when selecting from the table T1, Oracle will have to do data type conversion. When using the parameter values, Oracle will have to do a data type conversion because the parameter data type is NUMBER and the data type for the column is VARCHAR2. When Oracle does a data type conversion (such as to_number, to_char, to_date, etc.), it may happen that indexes, if such exist on the queried column, will be ignored. That ignoring of indexes accounts for the time variance between the preceding two examples.
You could do a profiling of the code to examine where the code uses more time in the first example compared to the second, but because the code is simple, you'll just examine what happens by looking at the SELECT statement. Start by setting AUTOTRACE ON for the session and then use the KEY value correctly—as a character—according to the data type of the column in the table.
SQL@V112> select value from T1 where key = '1';
VALUE
----------
package ST
Elapsed: 00:00:00.01
SQL@V112>
Examining the execution plan shows that the index PK_T1 is used.
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("KEY"='1')
In the next example, the KEY value is not correctly used according to the data type of the column in the table, but it would have been used in the first place, had I ignored the warning.
The execution plan for the statement now shows that the index is not used and that Oracle does a full table scan. Notice the filter in the Predicate Information; Oracle has added a TO_NUMBER to the KEY column, and therefore the index is not being used. Should there be characters in the KEY column, as the definition allows, the function would have failed with an ORA-01722: invalid number. Here is the plan output:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 14 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("KEY")=1)
SQL@V112>
As you see in this example, the correct defined function is approximately 20 times faster than the function with the parameter with the wrong data type. If warnings had not been enabled, you might not have noticed the wrong parameter data type and therefore not corrected the function. As you might notice, you can't at first distinguish whether a warning is an informational, performance, or severe warning. The way to determine the warning's category is to execute the dbms_warning.get_category function, like so:
SQL@V112> SELECT DBMS_WARNING.GET_CATEGORY(7204) FROM DUAL;
DBMS_WARNING.GET_CATEGORY(7204)
-----------------------------------------------------------------------
PERFORMANCE
As you work with warnings, you'll learn that severe warnings are in the range 5000 to 5999, informational are in the range 6000 to 6249, and performance in the range 7000 to 7249. Once you know the ranges, it becomes easy to classify the warnings by their numbers.
Promoting Warnings to Errors
As stated earlier, even though your functions or procedures are compiled with warnings, you are still able to execute them. If you want to ensure that stored procedures with specific warnings do not make it to you production system, you can promote these warnings to become errors.
The following code is what it looks like to compile the GET_T1_KEY function with the parameter as a NUMBER. Notice the warning that is generated.
SQL@V112> CREATE OR REPLACE FUNCTION GET_T1_KEY (P_KEY NUMBER) RETURN VARCHAR2
2 AUTHID DEFINER
3 IS
4 L_value T1.VALUE%TYPE;
5 BEGIN
6 SELECT VALUE INTO l_value FROM T1 WHERE KEY = P_KEY;
7 RETURN l_value;
8 EXCEPTIONS
9 WHEN NO_ROWS_FOUND THEN
10 RETURN 'No value found';
11 END;
12 /
SP2-0806: Function created with compilation warnings
SQL@V112> show err
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/49 PLW-07204: conversion away from column type may result in
sub-optimal query plan
To ensure that this kind of code is not being deployed—at least not without errors—you can promote this warning to become an error. First, alter the session disabling all warnings (I do it in this example to concentrate on the warning I want to promote). In the same statement, promote the PLW-07204 warning to become an error instead of a warning, like so:
SQL@V112> alter session set plsql_warnings='DISABLE:ALL, ERROR:7204';
Session altered.
Then recompile the GET_T1_KEY function.
SQL@V112> alter function GET_T1_KEY compile;
Warning: Function altered with compilation errors.
SQL@V112> show err
Errors for FUNCTION GET_T1_KEY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/45 PLS-07204: conversion away from column type may result in
sub-optimal query plan
Now try to execute the function. It will result in errors.
SQL@V112> exec :txt := get_t1_key(1);
BEGIN :txt := get_t1_key(1); END;
*
ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00905: object ACME.GET_T1_KEY is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Notice that PLW-07204 became PLS-07204. The warning is now an error, and you are unable to execute the function. To be able to execute the function, you will have to get rid of the error by changing the parameter type to VARCHAR2.
If you have an existing application with PL/SQL, and you enable warnings and recompile the PL/SQL code, you will probably get many warnings. Even though it feels good not to get any warnings, it may still, for one reason or another, be impossible to get rid of all of the warnings. Should warnings exist that you can't get rid of, you are free to ignore them.
Caution It can be tempting to make the elimination of all warnings a hard and fast goal. Non-technical managers especially can fall prey to that temptation, because they often don't understand the subtle difference between a warning and an error. Remember that warning messages come from a machine attempting to interpret your code against someone else's set of best practices. Warnings thus point to code that deserves to be examined more closely. However, sometimes you really do need to perform a certain action, and it would be a worst practice indeed to allow a compiler to coerce you into blindly following someone else's best practice.
Another small issue with warnings is that they clutter the USER_ERRORS view. Warnings are logged in the same view as errors. That may be annoying if you are used to checking whether the USER_ERRORS view is empty as a way to see whether you have errors in a give piece of PL/SQL code that you have just compiled. You can't eliminate this minor irritation. You must live with it, but it's a small price to pay for the value returned by the warnings feature.
Ignoring Warnings
You may want to ignore specific warnings for some reason. In the next example, all warnings are enabled. But one of the warnings can't be removed by changing the code, as the warning regards a column name conflicting with a reserved word, and you want to ignore this specific informational warning. To enable all warnings, set plsql_warnings to ENABLE:ALL, like so:
SQL@V112> alter session set plsql_warnings='ENABLE:ALL';
Next, create the following version of the GET_T1_KEY function:
SQL@V112>CREATE OR REPLACE FUNCTION GET_T1_KEY (P_KEY NUMBER) RETURN VARCHAR2
2 AUTHID DEFINER
3 IS
4 l_value T1.VALUE%TYPE;
5 l_1 NUMBER :=1;
6 l_2 NUMBER :=2;
7 BEGIN
8 IF l_1 = l_2 THEN
9 SELECT VALUE INTO l_value FROM T1 WHERE KEY = P_KEY;
10 RETURN l_value;
11 END IF;
12 END;
13 /
SP2-0806: Function created with compilation warnings
SQL@V112> show err
Errors for FUNCTION GET_T1_KEY:
0/0 PLW-06010: keyword "VALUE" used as a defined name
1/1 PLW-05005: subprogram GET_T1_KEY returns without value at line 11
9/11 PLW-06002: Unreachable code
9/51 PLW-07204: conversion away from column type may result in
sub-optimal query plan
Let's look at the warnings one at a time.
- PLW-06010: A PL/SQL or SQL word has been used as a defined name. This is legal but not recommended. Reserved words can be examined in the V$RESERVED_WORDS view.
- PLW-05005: Oracle discovers that you never enter the IF statement and therefore will not return a value from the function.
- PLW-06002: Again, Oracle discovers that you never enter the IF statement and therefore will have unreachable code. Unreachable code does no harm as such, but this could indicate inappropriate code. Having too much unreachable code will take up memory when you execute PL/SQL.
- PLW-07204: Well, you know what do with that by now.
So, fix the code and get rid of the warnings. Let's say that for some reason, you're unable to change the column name VALUE, which is the cause for PLW-06010; for example, your application might be old, with too much code to be fixed if you change the column name.
The first thing to do is to prepare the session.
SQL@V112> alter session set plsql_warnings='ENABLE:ALL, ERROR:7204, DISABLE:6010';
In this case, all warnings are enabled, warning 7204 is still promoted to be an error, and warning 6010 is disabled. The function can now be reapplied and compiled without errors or warnings.
If you want to examine the PL/SQL warnings that a stored procedure has been compiled with, you can issue the following query:
SQL@V112> col plsql_warnings for a40
SQL@V112> SELECT NAME, PLSQL_WARNINGS FROM USER_PLSQL_OBJECT_SETTINGS;
NAME PLSQL_WARNINGS
------------------------------ ----------------------------------------
GET_T1_KEY ENABLE:ALL,DISABLE: 6010,ERROR: 7204
These results show the PLSQL_WARNINGS parameter value that was in effect when GET_T1_KEY was compiled.
Compilation and Warnings
Whenever you create or recompile stored procedures, they inherit the settings from the current session. So should you by accident compile a stored procedure in a session where warnings have not been set up, the stored procedure will just compile and settings will be lost. If your database is configured with system wide warnings (ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL, ERROR:7204') and some of them have been promoted to be an error, the result may be more troublesome, as the recompiled procedure will fail. To recompile a procedure with its current settings, execute the following:
SQL@V112> ALTER FUNCTION GET_T1_KEY COMPILE REUSE SETTINGS;
Function Compiled.
Instead of having to execute an ALTER SESSION each time you want to compile a stored procedure with specific PL/SQL warnings, you can specify the warning setting at compile time.
SQL@V112> ALTER FUNCTION GET_T1_KEY COMPILE PLSQL_WARNINGS='ENABLE:ALL,
DISABLE:6010,ERROR: 7204';
Function Compiled.
If you have common warning settings for all stored procedures, you could add a login trigger to set PLSQL_WARNINGS at log in time. It's not uncommon that developers share the same database user to develop an application. In this case, the trigger could be quite simple and limited to that user only.
The trigger should, in this case, fire after login to the ACME schema. What the trigger does is an ALTER SESSION and thereby set the PLSQL_WARNINGS parameter.
Create the trigger connected as SYS.
SQL@V112> CREATE OR REPLACE TRIGGER dev_logon_trigger
2 AFTER LOGON ON ACME.SCHEMA
3 BEGIN
4 EXECUTE IMMEDIATE q'"ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE,ENABLE:PERFORMANCE,ERROR:7204'"';
5 END;
6 /
Trigger created.
Then connect as the Acme user.
SQL@V112> conn ACME/acme
Connected.
Check that the parameter has been set.
SQL@V112> SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;
GET_WARNING_SETTING_STRING
------------------------------------------------------------------------
DISABLE:INFORMATIONAL,ENABLE:PERFORMANCE,ENABLE:SEVERE,ERROR: 7204
If your application has many stored procedures and they have different settings, it may be a good idea to build a framework that will help you remember their individual settings and can be used for recompilation (see the small framework example in the upcoming section “Conditional Compilation”).
If you want to compile all stored procedures in a schema with the DBMS_UTILITY.COMPILE_SCHEMA procedure, you should be aware that the parameter reuse_settings is by default set to FALSE. The following is the description of the DBMS_UTILITY.COMPILE_SCHEMA procedure:
PROCEDURE COMPILE_SCHEMA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN
COMPILE_ALL BOOLEAN IN DEFAULT
REUSE_SETTINGS BOOLEAN IN DEFAULT
If you don't apply the reuse_settings parameter and set it to TRUE, all compiled packages will lose any warning settings. Therefore, when executing DBMS_UTILITY.COMPILE_SCHEMA, remember to set reuse_settings to TRUE, like so:
SQL@V112> exec DBMS_UTILITY.COMPILE_SCHEMA (schema=>'ACME', reuse_settings=>true);
PL/SQL procedure successfully completed.
Caution You should use caution if setting plsql_warnings on the SYSTEM level. Even though you can't compile SYS with DBMS_UTILITY.COMPILE_SCHEMA, you can still compile individual packages. Setting plsql_warnings at SYSTEM level, and then compiling just a few packages may result in hundreds of warnings or, should you have promoted some warnings to errors, in errors being thrown.
As SYS or SYSTEM you can run the utility utlrp (@?/rdbms/admin/utlrp), that will compile all invalid stored procedures. Utlrp calls UTL_RECOMP.RECOMP_PARALLEL to compile stored procedures in parallel. When doing this, warnings promoted to errors or warnings that are ignored get striped off, as you shall see in the following example. This is a bug.
First, set session plsql_warnings
SQL@V112> alter session set plsql_warnings='Enable:all, ERROR:7204, DISABLE:6010';
Session altered.
and compile a selected function.
SQL@V112> alter function GET_T1_KEY compile;
Warning: Function altered with compilation errors.
Next, take a look on the function settings.
SQL@V112> SELECT NAME, PLSQL_WARNINGS FROM USER_PLSQL_OBJECT_SETTINGS WHERE NAME =
'GET_T1_KEY';
NAME PLSQL_WARNINGS
------------------------------ -------------------------------------------------------------
---------------------------------------
GET_T1_KEY ENABLE:ALL,DISABLE: 6010,ERROR: 7204
Now, connect as SYS
SQL@V112> conn / as sysdba
Connected.
and execute the recompilation, either by calling utlrp.sql or as here by executing UTL_RECOMP.RECOMP_PARALLEL directly.
SQL@V112> exec utl_recomp.recomp_parallel(1,'ACME'),
PL/SQL procedure successfully completed.
Now reconnect
SQL@V112> conn ACME/ACME
Connected.
and examine the object settings.
SQL@V112> SELECT NAME, PLSQL_WARNINGS FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'GET_T1_KEY';
NAME PLSQL_WARNINGS
------------------------------ -------------------------------------------------------------
---------------------------------------
GET_T1_KEY ENABLE:ALL
ACME@V112>
Notice that specific warning settings have been removed; this must be a bug. It's not a disaster, as everything still works, but it's irritating because you now don't know if special warnings were set up.
If you export and import schemas using exp and imp, any stored code will be imported with the exported settings.
Final Words on Warnings
Other types of warnings you might encounter are warnings for not using the NOCOPY compiler directive when you will benefit from that. In Oracle 11g, there is a new, very useful warning for situations in which the exception WHEN OTHERS does not result in either RAISE or RAISE_APPLICATION_ERROR. I have personally spent hours wondering why I did not get an error in certain situations, just to experience that somebody did a WHEN OTHERS THEN NULL.
Conditional Compilation
Conditional compilation is not related to PL/SQL warnings, but it was implemented at the same time as PL/SQL warnings. Conditional compilation is done by preprocessing the source code, as you might know from other coding languages. In short, it gives you the ability to turn code on or off at compilation time, without having to edit the code. This could be turning on or off debug options or enabling code that has to do with this or that version of Oracle or perhaps two different editions of your application, such as a Standard Edition and Enterprise Edition.
Note Conditional compilation has been back-ported to Oracle 10.1.0.4 and 9.2.0.6 and can be enabled by setting the parameter _conditional_compilation.
Basics
Conditional compilation consists of a small number of directives. The directives starts with a $ (Dollar) sign. You use the directives to control the flow in the program or raise errors.
The system defined directives are: $IF, $THEN, $ELSIF, $ELSE and $END. You can raise your own compilation errors with the directive $ERROR … $END. You can inquire on static Boolean expressions or static constants. Besides creating your own inquire variables, Oracle has a few you can inquire, which you will see later.
The easiest way to get started with conditional compilation is to start with an example. This example will show you how to enable logging of timing information for a stored procedure, which measures how long it takes to run the procedure and when the procedure was running. The following is the example code (the lines are numbered for later reference):
1 CREATE OR REPLACE PROCEDURE LEVEL2 AS
2 $IF $$TIMING_ON $THEN
3 $ELSIF NOT $$TIMING_ON $THEN
4 $ELSE
5 $ERROR ' The PLSQL_CCFLAG TIMING_ON, Must be set to either FALSE or TRUE before
compileing procedure ' || $$PLSQL_UNIT||'.'
6 $END
7 $END
8 $IF $$TIMING_ON $THEN
9 L_start_time TIMESTAMP;
10 $END
11 BEGIN
12 DBMS_APPLICATION_INFO.SET_MODULE($$PLSQL_UNIT, 'RUN'),
13 $IF $$TIMING_ON $THEN
14 L_START_TIME:=CURRENT_TIMESTAMP;
15 $END
16 -- -----------------------------------------------
17 -- Application logic is left out
18 -- -----------------------------------------------
19 $IF $$TIMING_ON $THEN
20 LOGIT (P_PG=>$$PLSQL_UNIT, P_START_TIME=>l_start_time, P_STOP_TIME=>CURRENT_TIMESTAMP);
21 $END
22 DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
23 END LEVEL2;
24 /
The following is an explanation of the listing:
- Line 2-7: If the inquiry variable
$$TIMING_ONis not set at compile time (in other words, it's neither FALSE nor TRUE), then an error will be thrown, telling the name of the stored procedure and line where the error occurred. Notice that you are allowed to have empty statements—an $IF … $THEN without any action. - Line 8: If the inquiry variable
$$TIMING_ONequals to TRUE, then you declare the variablel_start_time.Notice that the $END has no $IF as in PL/SQL END IF; and no semicolon. - Line 12: Here you call DBMS_APPLICATION_INFO.SET_MODULE procedure, which will set the MODULE column in the V$SESSION view; you use the Oracle inquiry variable
$$PLSQL_UNITto hold the name of the stored procedure or, in case it is referenced in a PACKAGE, the name of the package. In prior Oracle versions, you would have had to create a function that executed the OWA_UTIL.WHO_CALLED_ME procedure that returns the name of the caller, then call that function to get the name. Calling the DBMS_APPLICATION_INFO.SET_MODULE is very helpful if you need to query V$SESSION to locate you session or if you are using Extended Trace in order to start tracing when the procedure starts.
Note If you are using SQL*Developer to develop your stored procedures, you can modify the template holding the CREATE PROCEDURE/FUNCTION template to include call to DBMS_APPLICATION_INFO.SET_MODULE($$PLSQL_UNIT, '<action>'), to ensure that it is always included. This is done from the menu Tools 
Preferences Database:SQL Code Editor Templates.
- Line 13: If the inquiry variable
$$TIMING_ONequals TRUE, then register the start timestamp in the variablel_start_time. - Line 16-18: This would be the PL/SQL code.
- Line 19: If the inquiry variable
$$TIMING_ONequals TRUE, then call the LOGIT procedure in line 20. The LOGIT procedure is the logging procedure that will log timing information. The procedure is not described here as it isn't important. - Line 22: Clear the DBMS_APPLICATION_INFO call.
Before the procedure is added to the database, the compiler flag TIMING_ON needs to be set. You can set that flag either at the system, session, or program level. In this example, it's set at the session level. Before you set the flag, try to add the code to the database to provoke the $ERROR error (assume that the code is saved in a file called level2.sql).
Do a show error to reveal the error, like so:
SQL@V102> show error
Errors for PROCEDURE LEVEL2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4 PLS-00179: $ERROR: The PLSQL_CCFLAG TIMING_ON, Must be set to
either FALSE or TRUE before compileing procedure LEVEL2.
Now set the flag, like so:
SQL@V112> ALTER SESSION SET plsql_ccflags='TIMING_ON:TRUE';
The data type of the plsql_ccflags is either a Boolean static expression, PLS_INTEGER static expression, or VARCHAR2 static expression, depending on what you assign to it. Notice that setting plsql_ccflags will not enforce recompilation of any code; an ALTER PROCEDURE … COMPILE would have to be issued.
SQL@V102> ALTER PROCEDURE LEVEL2 COMPILE;
Procedure altered.
Note Remember that every stored procedure that is compiled after the session plsql_ccflags has been set will inherit the plsql_ccflags setting.
To examine what plsql_ccflags was set to during a stored procedure compilation, execute the following query:
SQL@V102> col plsql_ccflags for a80 wrap
SQL@V102> select NAME, PLSQL_CCFLAGS FROM USER_PLSQL_OBJECT_SETTINGS;
NAME PLSQL_CCFLAGS
------------------------------ --------------------------------------------
LEVEL1
LEVEL2 TIMING_ON:TRUE
LOGIT
…
As shown in the example below, if you have more than one plsql_ccflag set at a time, they must be separated by commas. The plsql_ccflag value is case insensitive.
SQL@V112> ALTER SESSION SET plsql_ccflags='TIMING_ON:TRUE,Standard:True';
What Part of the Code is Running?
In the simple procedure just shown in the “Basics” section, it's easy to get the overview of what code is actually running whether the TIMING_ON flag is set to TRUE or FALSE. The USER_SOURCE view reveals all the code in the stored procedure. The conditional compilation code you create may be very complex, so to see what code is actually compiled or not, you should call the DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE procedure. This procedure generates the code that is actually compiled (remember to set serveroutput on).
SQL@V112> CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE', 'ACME','LEVEL2'),
PROCEDURE LEVEL2 AS
L_start_time TIMESTAMP;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE( 'LEVEL2' , 'RUN'),
L_start_time:=CURRENT_TIMESTAMP;
-- -----------------------------------------------
-- Application logic is left out
-- -----------------------------------------------
LOGIT (P_PG=> 'LEVEL2' , P_START_TIME=>l_start_time, P_STOP_TIME=>CURRENT_TIMESTAMP);
DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
END LEVEL2;
Call completed.
The code presented is without any of the formatting you may have applied to the code. Database objects in code that are not present in the preprocessed code will not be visible in the USER_DEPENDENCIES view. Wrapped code remains wrapped and you can't unwrap code using this procedure. (You can read more about dependencies in Chapter 15.)
If you want to process the pre-processed code yourself, you can call the DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE function, which will return a table of VARCHAR2 containing the code. The reason that formatting is lost it is because the package uses DBMS_OUTPUT.PUT_LINE. The PUT_LINE procedure removes leading spaces. If you create a procedure like the one below, adding a line number (or any character) to the left of the code will ensure that formatting is not lost. The following procedure—PRINT_SOURCE—will print the preprocessed code with formatting and line numbers, based on the parameters given:
SQL@V112> CREATE OR REPLACE PROCEDURE PRINT_SOURCE (P_OBJECT_TYPE VARCHAR2, P_SCHEMA_NAME
VARCHAR2, P_OBJECT_NAME VARCHAR2) IS
2 l_source_lines DBMS_PREPROCESSOR.source_lines_t;
3 BEGIN
4 l_source_lines := DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE(P_OBJECT_TYPE,
P_SCHEMA_NAME, P_OBJECT_NAME);
5 FOR I IN l_source_lines.FIRST .. l_source_lines.LAST LOOP
6 IF TRIM(SUBSTR(l_source_lines (i),1, length(l_source_lines (i))-1)) IS NOT NULL
THEN
7 DBMS_OUTPUT.PUT_LINE(RPAD(I,4, ' ')||REPLACE(l_source_lines(i), CHR(10),''));
8 END IF;
9 END LOOP;
10 END;
11 /
Executing the procedure with the given parameters results in the following output:
SQL@V112> set serveroutput on
SQL@V112> exec PRINT_SOURCE ('PROCEDURE','ACME','LEVEL2'),
1 PROCEDURE LEVEL2 AS
8 l_start_time TIMESTAMP;
10 BEGIN
11 DBMS_APPLICATION_INFO.SET_MODULE( 'LEVEL2' , 'RUN'),
13 l_start_time:=CURRENT_TIMESTAMP;
15 -- -----------------------------------------------
16 -- Application logic is left out
17 -- -----------------------------------------------
19 LOGIT (P_PG=> 'LEVEL2' , P_START_TIME=> l_start_time,
P_STOP_TIME=>CURRENT_TIMESTAMP);
21 DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
22 END LEVEL2;
Benefits of Preprocessing Code
One of the benefits you gain by excluding code with preprocessed code is that the amount of memory used by the executing code may be reduced, thus less memory is used in the shared pool. Suppose, for example, that the procedure LEVEL2 was coded as follows:
SQL@V112> CREATE OR REPLACE PROCEDURE LEVEL2 (TIMING_ON BOOLEAN := FALSE) AS
2 l_start_time TIMESTAMP;
3 BEGIN
4 DBMS_APPLICATION_INFO.SET_MODULE('LEVEL2' ,NULL);
5 IF TIMING_ON THEN
6 l_start_time:=CURRENT_TIMESTAMP;
7 END IF;
8 -- -----------------------------------------------
9 -- Application logic is left out
10 -- -----------------------------------------------
11 IF TIMING_ON THEN
12 LOGIT (P_PG=>'LEVEL2', P_START_TIME=>l_start_time,
P_STOP_TIME=>CURRENT_TIMESTAMP);
13 END IF;
14 DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
15 END LEVEL2;
16 /
This example shows how you might code a normal stored procedure, in which you would enable some timing measurements. The view USER_OBJECT_SIZE will show an estimate of how much memory in bytes a given program will require. However, you can't see how much memory the program requires at runtime, as runtime memory usage is dependent on how you write the code, whether you do bulk collects, and other such things. The following is the memory usage estimation for LEVEL2:
SQL@V112> SELECT * FROM USER_OBJECT_SIZE WHERE NAME = 'LEVEL2';
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE
-------------- ------------- ----------- ----------- ---------- ----------
LEVEL2 PROCEDURE 521 996 510 0
The column SOURCE_SIZE shows the size of the source code for the given procedure. Source code must fit in memory during compilation. PARSED_SIZE is how much memory the given program requires when other programs that are compiled reference it. CODE_SIZE is the amount of memory that the program requires in order to execute— as a start. And finally, the column ERROR_SIZE is the number of bytes in memory for error messages during compilation. In this case, ERROR_SIZE is 0 because no errors occurred during compilation.
Suppose you create the same procedure, but let a PL/SQL compiler flag control the timing measurement call, so that the code looks like this:
SQL@V112> CREATE OR REPLACE PROCEDURE LEVEL2 AS
2 $IF $$TIMING_ON $THEN
3 l_start_time TIMESTAMP;
4 $END
5 BEGIN
6 DBMS_APPLICATION_INFO.SET_MODULE($$PLSQL_UNIT ,NULL);
7 $IF $$TIMING_ON $THEN
8 l_start_time:=CURRENT_TIMESTAMP;
9 $END
10 -- -----------------------------------------------
11 -- Application logic is left out
12 -- -----------------------------------------------
13 $IF $$TIMING_ON $THEN
14 LOGIT (P_PG=>$$PLSQL_UNIT, P_START_TIME=>l_start_time,
P_STOP_TIME=>CURRENT_TIMESTAMP);
15 $END
16 DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
17 END LEVEL2;
18 /
Procedure created.
Setting the PL/SQL flag TIMING_ON to FALSE results in the following code sizes:
SQL@V112> SELECT * FROM USER_OBJECT_SIZE WHERE NAME = 'LEVEL2';
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE
------------- ------------- ----------- ----------- ---------- ----------
LEVEL2 PROCEDURE 530 857 364 0
The SOURCE_SIZE is almost the same size as in the first example, even though $IF constructions have been added to control definition and reference of the variable l_start_time. The first example also has the parameter parsing in the code, so there's not much difference there either. But the PARSED_SIZE and CODE_SIZE values have both been reduced.
Now let's look at the case when timing is enabled. Compiling the procedure LEVEL2 with the compiler flag TIMING_ON set to TRUE gives the following result:
SQL@V112> SELECT * FROM USER_OBJECT_SIZE WHERE NAME = 'LEVEL2';
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE
------------- ------------- ----------- ----------- ---------- ----------
LEVEL2 PROCEDURE 530 917 478 0
Compared to the code that was compiled with TIMING_ON set to FALSE, the SOURCE_SIZE does not differ, but the PARSED_SIZE and CODE_SIZE values have been increased slightly. This is because of the call to the procedure LOGIT.
Now take a look now at the size of the original LEVEL2 procedure, this one with TIMING_ON set to TRUE.
SQL@V102> select * from USER_OBJECT_SIZE WHERE NAME = 'LEVEL2';
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE
------------- ------------- ----------- ----------- ---------- ----------
LEVEL2 PROCEDURE 730 917 497 0
The SOURCE_SIZE is larger than the LEVEL2 procedure without conditional compilation, but the PARSED_SIZE and CODE_SIZE are the same, or nearly so, as the limited LEVEL2 with conditional compilation. This is because the conditional compilation code is stripped during the compilation. Thus, you can see that using conditional compilation results in a bigger footprint in source and during compilation (and also during automatic recompilation triggered by dependencies). If Oracle executes an automatic recompilation, the REUSE SETTINGS compile option is used.
So, without conditional compilation, you get this:
SQL@V102> exec level2
Elapsed: 00:00:20.60
And with conditional compilation (TIMING_ON set to TRUE), you get this:
SQL@V102> exec level2
Elapsed: 00:00:20.49
Here the difference is only a matter of fragments of seconds. Such a small period of time is an inconsequential price to pay in return for the benefits of conditional compilation.
Invalidations
Objects dependent on other objects may be invalidated if the objects they dependent on change or get invalidated for some reason. Let's expand this idea a bit by creating a LEVEL1 procedure that calls the LEVEL2 procedure. The first example is running in Oracle 10.2.
SQL@V102> CREATE OR REPLACE PROCEDURE LEVEL1 AS
2 BEGIN
3 LEVEL2;
4 END;
5 /
Procedure created.
When you have to compile a procedure at a lower level, Oracle will invalidate the procedure at the higher level if dependencies exists and thereby force an automatic recompilation the next time the higher level procedure is called—if it's not compiled manually beforehand, that is.
After creating the LEVEL1 procedure, recompile the LEVEL2 procedure.
SQL@V102> ALTER PROCEDURE LEVEL2 COMPILE REUSE SETTINGS;
Procedure altered.
Then examine the STATUS of the procedures.
SQL@V102> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN
('LEVEL1','LEVEL2'),
OBJECT_NAME STATUS
------------------------------ -------
LEVEL1 INVALID
LEVEL2 VALID
Executing the LEVEL1 procedure will enforce a recompilation and validate the procedure.
SQL@V102> exec LEVEL1
PL/SQL procedure successfully completed.
SQL@V102> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN
('LEVEL1','LEVEL2'),
OBJECT_NAME STATUS
------------------------------ -------
LEVEL1 VALID
LEVEL2 VALID
The LEVEL1 procedure has been dynamically recompiled and is now valid again. This has nothing to do with conditional compilation—that's just how it works.
Caution Having hundreds of stored procedures calling one another in a system and then making a recompilation either on purpose or indirectly of a procedure may be a disaster. Let me emphasize this with a true story. In a company with hundreds of concurrent users, a manual update of a table had gone wrong—a column was set to a wrong value. So the Database Administrator created a copy of the table with a SELECT AS OF TIMESTAMP. So far, everything was fine. Then he renamed the original table and quickly renamed the copy of the table to the original name. Well, Oracle will notice such a swapping of names, should there be any dependencies. In this case, this table was referenced by a low level procedure, which went invalid, causing all procedures that relied on this procedure to go invalid as well. This made almost all user sessions line up for recompilation of the code. The database had to be restarted, as nobody could work on the system. After the restart, the system worked as usual.
In Oracle 11g, Oracle has loosened the rules for invalidation of dependent objects. If you don't change the footprint of the parameter of a procedure, Oracle will not force an invalidation of dependent objects, whether it encounters procedures in packages or stand-alone procedures. If you rerun the preceding example involving LEVEL1 and LEVEL2 in Oracle 11g rather than Oracle 10g, the result will look like the following:
SQL@V112> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN
('LEVEL1','LEVEL2'),
OBJECT_NAME STATUS
------------------------------ -------
LEVEL1 VALID
LEVEL2 VALID
SQL@V112> ALTER PROCEDURE LEVEL2 COMPILE REUSE SETTINGS;
Procedure altered.
SQL@V112> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN
('LEVEL1','LEVEL2'),
OBJECT_NAME STATUS
------------------------------ -------
LEVEL1 VALID
LEVEL2 VALID
SQL@V112>
Notice that both procedures end up as valid. Because you are in Oracle 11g and because your procedure footprints have not changed, Oracle does not force a recompile.
Controlling Compilation
Having different plsql_ccflags for each procedure—and thus a large number of compiler flags—may be confusing. Even though you may execute an ALTER PROCEDURE <procedure name> COMPILE REUSE SETTINGS, at some point you may accidently compile a procedure without having set the appropriate compiler flags. And if you don't check in your conditional compilation code whether a ccflag is set, your code may compile with the “wrong” setting. A way to avoid this could be to build a small framework that controls the recompilation, as in the following example:
SQL@V112> CREATE TABLE CC_FLAGS (
2 OWNER VARCHAR2(30),
3 OBJECT_TYPE VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 CCFLAGS VARCHAR2(4000));
Table Created.
Add the rows with information for each procedure.
SQL@V112> INSERT INTO CC_FLAGS (OWNER, OBJECT_TYPE, OBJECT_NAME, CCFLAGS) VALUES('ACME' ,
'PROCEDURE', 'LEVEL2', 'TIMING_ON:FALSE'),
1 Row created.
SQL@V112> INSERT INTO CC_FLAGS (OWNER, OBJECT_TYPE, OBJECT_NAME, CCFLAGS) VALUES('ACME' ,
'PROCEDURE', 'LEVEL1', ''),
1 Row created.
SQL@V112> COMMIT;
Commit complete.
SQL@V112> BEGIN
2 FOR I IN (SELECT * FROM CC_FLAGS) LOOP
3 EXECUTE IMMEDIATE 'ALTER ' ||I.OBJECT_TYPE||' '||I.OWNER||'.'||I.OBJECT_NAME||' COMPILE
PLSQL_CCFLAGS='''||I.CCFLAGS||'''';
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
So after running this anonymous PL/SQL block, the procedures are compiled with their settings. This could (or should) perhaps be a stored procedure, perhaps even with parameters to control whether one or all should be compiled; if so, don't put that procedure in the CC_FLAGS table or you will end up in a deadlock situation when the running procedure tries to compile itself.
Compiling a stored procedure with special plsql_ccflags by hand would look like the following example:
SQL@V112> ALTER PROCEDURE SPECIAL1 COMPILE PLSQL_CCFLAGS='SPECIAL_ONE:42';
Procedure altered.
In your development environment, you could use this framework and from it generate deployment scripts. If some stored procedure after deployment needs to be compiled with special flags, say you need to debug a procedure, it can be done as shown.
When creating deployment scripts, I ensure that I set the PLSQL_CCFLAGS, PLSQL_WARNINGS before I add stored procedures; should there be different flags and warnings, I set them before each stored procedure, as shown in the following code:
…
ALTER SESSION SET PLSQL_CCFLAGS='TIMING_ON:FLASE';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE,ENABLE_PERFORMANCE,ERROR:7204';
@@ GET_T1_KEY
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE,ENABLE:PERFORMANCE';
@@ SET_T1_KEY
…
Inquiry Variables
So far, I've been talking about the plsql_ccflags parameter and conditional compilations. You can use conditional compilations without having to set the plsql_ccflags parameter. You do that by querying your own package constants or those defined by Oracle. Let's work through an example.
First, you create a PACKAGE specification holding a constant named TIMING_ON set to the value FALSE.
SQL@V112> CREATE OR REPLACE PACKAGE TIMING AS
2 TIMING_ON CONSTANT BOOLEAN := FALSE;
3 END;
4 /
Then you recreate the stored procedure referencing the PACKAGE constant.
TIMING.TIMING_ON
SQL@V112> CREATE OR REPLACE PROCEDURE LEVEL2 AS
2 $IF TIMING.TIMING_ON $THEN
3 L_START_TIME TIMESTAMP;
4 $END
5 BEGIN
6 DBMS_APPLICATION_INFO.SET_MODULE($$PLSQL_UNIT ,NULL);
7 $IF TIMING.TIMING_ON $THEN
8 L_START_TIME:=CURRENT_TIMESTAMP;
9 $END
10 -- -----------------------------------------------
11 -- Application logic is left out
12 -- -----------------------------------------------
13 $IF TIMING.TIMING_ON $THEN
14 LOGIT (P_PG=>$$PLSQL_UNIT, P_START_TIME=>L_START_TIME, P_STOP_TIME=>CURRENT_TIMESTAMP);
15 $END
16 DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
17 END LEVEL2;
18 /
Procedure created.
Calling the DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE shows you that the code has been preprocessed with TIMING_ON = FALSE, as expected.
SQL@V112> CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE', 'SCOTT','LEVEL2'),
PROCEDURE LEVEL2 AS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE( 'LEVEL2' ,NULL);
-- -----------------------------------------------
-- Application logic is left out
-- -----------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
END LEVEL2;
Call completed.
Changing the PACKAGE constant to TRUE will set all stored procedures that reference this package to an invalid state. Hence, they all need to be recompiled.
SQL@V112> CREATE OR REPLACE PACKAGE TIMING AS
2 TIMING_ON CONSTANT BOOLEAN := TRUE;
3 END;
4 /
SQL@V112> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME IN
('TIMING','LEVEL1','LEVEL2'),
OBJECT_NAME STATUS
------------------------------ -------
TIMING VALID
LEVEL1 INVALID
LEVEL2 INVALID
After the recompilation of the code, it will match the TIMING_ON value. In this case, you have enabled timing measurements in all dependent stored procedures. Bear in mind the warning that I gave previously regarding recompiling all your code in a production environment.
Final Words on Conditional Compilation
Oracle offers a number of enquiry variables. Besides the $$PLSQL_UNIT enquiry variable, the $$PLSQL_LINE is becoming one of my favorites because it can be used for debugging.
$$PLSQL_CCFLAGShold the current PLSQL_CCFLAGS.$$PLSQL_WARNINGSholds the current plsql_warnings setting.$$PLSQL_OPTIMIZE_LEVELholds the PLSQL_OPTIMIZE_LEVEL parameter value.$$PLSQL_LINEholds the line number where the $$PLSQL_LINE variable is located.$$PLSCOPE_SETTINGholds the current value of the session PLSCOPE_SETTINGS parameter.$$PLSQL_CODE_TYPEholds the current value of the parameterplsql_code_type. Either NATIVE or INTERPRETED.$$NLS_LENGTH_SEMANTICSholds the NLS lengths semantics is BYTE or CHAR.
As a “normal” user, you're not able to execute the command show parameter <parameter> to show the value of the parameters such as plsql_ccflags or plsql_warnings. Therefore, these enquiry variables may come in handy. Create a script with an anonymous PL/SQL block and execute the script to show the value of inquiry variables in the current session, like so:
set serveroutput on
BEGIN
dbms_output.put_line('PLSQL Unit : ' ||$$PLSQL_UNIT);
dbms_output.put_line('PLSQL CCFLAGS : ' ||$$PLSQL_CCFLAGS);
dbms_output.put_line('PLSQL Warnings : ' ||$$PLSQL_Warnings);
dbms_output.put_line('Optimize Level : ' ||$$PLSQL_OPTIMIZE_LEVEL);
dbms_output.put_line('Line: ' ||$$PLSQL_LINE);
dbms_output.put_line('PLSCOPE setting: ' ||$$PLSCOPE_SETTING);
dbms_output.put_line('PLSQL code type: ' ||$$PLSQL_CODE_TYPE);
dbms_output.put_line('NLS length semantics: ' ||$$NLS_LENGTH_SEMANTICS);
END;
/
Execute the script to see the value of the inquiry variables.
SQL@V112> @plsql_inqvar.sql
PLSQL Unit :
PLSQL CCFLAGS : TIMING_ON:TRUE
PLSQL Warnings : DISABLE:INFORMATIONAL,ENABLE:PERFORMANCE,ENABLE:SEVERE,ERROR: 7204
Optimize Level: 2
Line : 6
PLSCOPE setting:
PLSQL code type: INTERPRETED
NLS length semantics: BYTE
PL/SQL procedure successfully completed.
With this small script you are able to see the current value of the inquiry variables. The $$PLSQL_UNIT inquiry variable is empty, as its value is being queried from an anonymous block.
Note The PL/SQL inquiry variables are evaluated and translated at compilation time, so having this in a stored procedure would require a recompilation of the procedure if any of the flags have changed, in order to show the correct value.
You could issue the following command to show the current value of $$PLSQL_CCFLAGS:
SQL@V112> exec dbms_output.put_line($$PLSQL_CCFLAGS);
TIMING_ON:TRUE
One common use of conditional compilation is to enable specific code, depending on the Oracle version; in other words, to define variables as either NUMBER or BINARY_FLOAT, like so:
...
$IF DBMS_DB_VERSION.VERSION >=11 $THEN
VAR BINARY_FLOAT;
$ELSE
VAR NUMBER;
$END;
...
In the Package specification of DBMS_DB_VERSION, there are a number of Boolean “version” constants that can be used to include or exclude code.
Putting it a bit to the extreme you can make parameters to stored procedures conditional as shown in the example here.
SQL@V112> CREATE PROCEDURE EXTREME (P_PARAM VARCHAR2 $IF $$INQ_FLAG $THEN, P_PARAM2 VARCHAR2 $END) IS
2 BEGIN
3 NULL;
4 END;
5 /
So depending on the $INQ_FLAG you would have either one or two parameters - it can be done, but not advisable.
Summary
In this chapter, you learned what you may gain from enabling warnings. You looked into how to enable warnings at the session, system, or procedure level. You have explored how to either promote warnings to become errors or how to ignore them by disabling them. You saw that PL/SQL warnings would certainly help you to make your code perform better and make it more robust. It will, though, give you an extra administrative task, especially if you have too many exceptions for which warnings should be enabled, disabled, or promoted to be errors.
Note You will not necessarily have to enable PL/SQL warnings on your production system if your code is checked in the development environment.
In looking at conditional compilation, you saw that using conditional compilation can limit the size of memory used by PL/SQL by only enabling logging or debugging information when required. You also saw that by using conditional compilation and setting a flag and performing a recompile, you were able to enable or disable code without having to edit the code. Conditional compilation is widely used in languages such as C++. Now you can have it as part of your PL/SQL toolbox, too.