All About PL/SQL Compilation Settings

A recent Twitter thread delved into the topic of the best way to enable PL/SQL warnings for program units, including this recommendation from Bryn Llewellyn, Distinguished Product Manager for PL/SQL:

which then led to Bryn volunteering me to delve into the details of PL/SQL compiler settings in an AskTOM PL/SQL Office Hours session. 

Which I will do. But I figured I could start right off by writing this post. So let's explore how to set and modify PL/SQL compiler settings.

First, you might wonder what those settings are or could be. The best way to check is by examining the USER_PLSQL_OBJECT_SETTINGS view (and of course the ALL* version to examine attributes of code you do not own but can execute):

The values that are stored for a PL/SQL unit are set every time it is compiled—in response to "create", "create or replace", "alter", invoking a utility like Utl_Recomp, or implicitly as a side effect of trying to execute an invalid PL/SQL unit.

A special case of "set" is to set the exact same values that were already stored for the unit. There's no way to ask for this outcome explicitly as part of the "create" or "create or replace" DDLs. It's the programmer's responsibility to ensure that the set of required values obtains in the session at the moment of compilation. There is a way to ask for this outcome with "alter". It's to say "reuse settings" (and not to mention any settings explicitly). More on this below. Notice that Utl_Recomp and its cousins, and implicit recompilation use the plain "reuse settings" mode of "alter".

I will explore how to set these attributes at both session and program unit level below, how to override, and how to preserve them. You can run all of this code yourself on LiveSQL.

When I first connect to a schema, and until I issue any ALTER statements, compilations of code will rely on the system defaults. You can see what they are by running the following script (thanks to Bryn Llewellyn for providing it):

declare
  Plsql_Debug constant varchar2(5) not null :=
    case
      when     $$Plsql_Debug then 'TRUE'
      when not $$Plsql_Debug then 'FALSE'
      else                        'illegal'
  end;
  Plsql_CCflags constant varchar2(4000) not null :=
    case
      when $$Plsql_CCflags is null then '[Not Set]'
      else                               $$Plsql_CCflags
  end;
begin
  if Plsql_CCflags = 'illegal' then
    raise Program_Error;
  end if;

  Sys.DBMS_Output.Put_Line('Plsql_Optimize_Level:' ||To_Char($$Plsql_Optimize_Level, '9'));
  Sys.DBMS_Output.Put_Line('Plsql_Code_Type:      '||$$Plsql_Code_Type);
  Sys.DBMS_Output.Put_Line('Plsql_Debug:          '||Plsql_Debug);
  Sys.DBMS_Output.Put_Line('Plsql_Warnings:       '||$$Plsql_Warnings);
  Sys.DBMS_Output.Put_Line('NLS_Length_Semantics: '||$$NLS_Length_Semantics);
  Sys.DBMS_Output.Put_Line('Plsql_CCflags:        '||Plsql_CCflags);
  Sys.DBMS_Output.Put_Line('Plscope_Settings:     '||$$Plscope_Settings);
end;

You can also use this query, as offered up by Connor McDonald:

select name, value
from v$parameter
where  name like 'plsql%'
    or name like 'plscope%'
    or name like 'nls_length_semantics%';

Here's the LiveSQL script that performs these two steps.

Note: I hope to update this post soon with a query that does not require you to create a database object first. :-)

Now let's take a look at how you can change the compilation settings for a program unit, at the session level and also for specifics. 

There are three ways you can compile an individual program unit:

1. CREATE OR REPLACE DDL statement
2. ALTER-COMPILE statement
3. DBMS_DDL.ALTER_COMPILE

The third option is really just an API to the ALTER-COMPILE statement.

When you compile your code via CREATE OR REPLACE that program unit will inherit all the current settings in your session.

With both ALTER-COMPILE and DBMS_DDL.ALTER_COMPILE, you can either inherit those settings or reuse (re-apply) the settings that are currently associated with the program unit.

All right, then, let's get going. I connect to my schema and immediately change the setting for PL/SQL compile-time warnings. I then compile my procedure and confirm that it used the session setting.

ALTER SESSION SET plsql_warnings = 'ENABLE:ALL'
/

CREATE OR REPLACE PROCEDURE aftercompile
   AUTHID DEFINER
IS
BEGIN
   NULL;
END;
/

SELECT plsql_warnings "From Session"
  FROM user_plsql_object_settings
 WHERE name = 'AFTERCOMPILE'
/

From Session
------------
ENABLE:ALL

Now I am going to recompile that procedure with an ALTER statement and specify a value different from the session's for PL/SQL warnings, but re-use all other settings. ALTER takes all the settings values from the environment. If you mention only some, and don't say "reuse settings", then it takes what you don't mention from the environment. As you can see in the query, the procedure now has PL/SQL warnings set to treat any warning as a compile error.

ALTER PROCEDURE aftercompile COMPILE plsql_warnings = 'ERROR:ALL' REUSE SETTINGS
/

SELECT plsql_warnings "From Procedure Override"
  FROM user_plsql_object_settings
 WHERE name = 'AFTERCOMPILE'
/

From Procedure Override
-------------------------
ERROR:ALL

Now I CREATE OR REPLACE to demonstrate that the session setting is now applied to the procedure:

CREATE OR REPLACE PROCEDURE aftercompile
   AUTHID DEFINER
IS
BEGIN
   NULL;
END;
/

SELECT plsql_warnings "Compile From Source"
  FROM user_plsql_object_settings
 WHERE name = 'AFTERCOMPILE'
/

Compile From Source
-------------------
ENABLE:ALL

But what if you want to recompile your program unit and you do not want to pick up the current session settings? You want to keep all settings intact. We offer you the REUSE SETTINGS clause.

From the last sequence of statements we can see that the session setting for PL/SQL warnings is "ENABLE:ALL". Below, I recompile my procedure, specifying "ERROR:ALL". Then I recompile again, but this time I do not specify a value for PL/SQL warnings. Instead I ask to reuse settings for the procedure.

ALTER PROCEDURE aftercompile COMPILE plsql_warnings = 'ERROR:ALL'
/

SELECT plsql_warnings "Back to Procedure Override"
  FROM user_plsql_object_settings
 WHERE name = 'AFTERCOMPILE'
/

Back to Procedure Override
--------------------------
ERROR:ALL

ALTER SESSION SET plsql_warnings = 'ENABLE:ALL'
/

ALTER PROCEDURE aftercompile COMPILE REUSE SETTINGS
/

SELECT plsql_warnings "Session Change No Impact with REUSE SETTINGS"
  FROM user_plsql_object_settings
 WHERE name = 'AFTERCOMPILE'
/

Session Change No Impact with REUSE SETTINGS
--------------------------------------------
ERROR:ALL

As you can see, the setting for my procedure did not change.

OK, I think that covers this territory pretty well (until my readers point out what I missed!).

Here are some links you might find helpful.

The COMPILE Clause

Conditional Compilation

Many thanks for the close reading and numerous suggestions for improvement from Bryn Llewellyn.