Compiling an Oracle Schema with Debug Information

Question

We have a script that creates several new Oracle packages, triggers, views, and functions in the Oracle database 12.1.0.2.0.

After that, all these objects are compiled. First we used DBMS_UTILITY.COMPILE_SCHEMA to compile all of these objects. However, COMPILE_SCHEMA does not add debug information. We would also like to add debugging information.

What is the best way to do this? This is our current algorithm:

• Create all objects
• Compile all objects in debug mode one by one. This leaves several objects invalid that have references to objects that appeared later in the list.
• Recompile everything using DBMS_UTILITY.COMPILE_SCHEMA so that all objects are valid.

In this case, all objects are compiled twice, which is clearly not optimal. There are many objects, so it takes a lot of time. We want to speed it up.

Is there anything available that does the same as DBMS_UTILITY.COMPILE_SCHEMA, but with debugging information turned on?

Answer 1

As stated in the documentation :

DEBUG

It has the same effect as PLSQL_OPTIMIZE_LEVEL=1- it creates a PL / SQL compiler to create and store code for use by the PL / SQL debugger. Oracle recommends using PLSQL_OPTIMIZE_LEVEL=1DEBUG instead.

, , , , . , SQL Developer , PLSQL_DEBUG true. :

create or replace package p42 as
  function f return number;
end p42;
/

create or replace package body p42 as
  function f return number is
  begin
    return 42;
  end f;
end p42;
/

select name, type, plsql_optimize_level, plsql_debug
from user_plsql_object_settings where name = 'P42';

NAME                           TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_DEBUG
------------------------------ ------------ -------------------- -----------
P42                            PACKAGE                         2 FALSE       
P42                            PACKAGE BODY                    2 FALSE       


alter session set plsql_optimize_level = 1;
alter session set plsql_debug = true;

exec dbms_utility.compile_schema(user);

select name, type, plsql_optimize_level, plsql_debug
from user_plsql_object_settings where name = 'P42';

NAME                           TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_DEBUG
------------------------------ ------------ -------------------- -----------
P42                            PACKAGE                         1 TRUE        
P42                            PACKAGE BODY                    1 TRUE        

, , , , , , , , . , :

DBMS_UTILITY.COMPILE_SCHEMA(schema => user, reuse_settings => true);

... :

DBMS_UTILITY.COMPILE_SCHEMA(schema => user, compile_all => false, reuse_settings => true);

Answer 2

SELECT PO.OWNER,PO.OBJECT_NAME,PO.OBJECT_TYPE,PO.DEBUGINFO
,'ALTER ' || REPLACE(object_type,'PACKAGE BODY','PACKAGE') || ' ' || owner || '.' || object_name ||DECODE(object_type,'PACKAGE BODY',' COMPILE BODY;',' COMPILE;') COPILE_NO_DEBUG
,'ALTER ' || REPLACE(object_type,'PACKAGE BODY','PACKAGE') || ' ' || owner || '.' || object_name ||DECODE(object_type,'PACKAGE BODY',' COMPILE DEBUG BODY;',' COMPILE DEBUG;') COPILE_WITH_DEBUG
FROM   SYS.ALL_PROBE_OBJECTS PO
--WHERE OBJECT_NAME='YOUR_DEFINITION_PKG' AND DEBUGINFO IN ('F','T')
ORDER BY owner, object_type, object_name;