On the Subject of the utlirplscope.sql script

By now you've probably realized that this is not the utlirplscope.sql  script promised in the Oracle 11.1.0.6 PL/Scope documentation.  There is a good reason for that.  Instead of simply providing a script to recompile STANDARD and DBMS_STANDARD for PL/Scope, we would first like to clear up some misconceptions about the usefulness of such a script and make sure you really do need to perform such an operation.

What was utlirplscope.sql?

The utlirplscope.sql was proposed to allow DBAs to safely recompile STANDARD and DBMS_STANDARD with PL/Scope identifier data.   Unfortunately, after the documentation was released, it was deemed easier to document the procedure by which STANDARD and DBMS_STANDARD could be safely recompiled, and so utlirplscope.sql was never written and thus does not exist.  For users of Oracle 11.1.0.6, the procedure to safely compile STANDARD and DBMS_STANDARD (the meat of the proposed utlirplscope.sql script) is documented at the end of this page.

Utlirplscope.sql was not meant to be a PL/Scope installation script.  PL/Scope is built into the PL/SQL compiler and does not require installation.

Although no PL/Scope identifier data is shipped in Oracle 11.1.0.6, you may begin collecting and using PL/Scope identifier data for your code base at any time, as described in the PL/Scope documentation, regardless of whether or not your database contains STANDARD and DBMS_STANDARD PL/Scope identifier data.

The need to recompile STANDARD and DBMS_STANDARD with PL/Scope identifier data only exists only for users of Oracle 11.1.0.6.  Users of Oracle 11.1.0.7 (except those that upgraded from Oracle 11.1.0.6) will have STANDARD and DBMS_STANDARD identifier data in their database by default, and will NOT have to execute the procedure documented on this page.  Oracle 11.1.0.7 PL/Scope documentation also contains this procedure to assist users who upgraded from Oracle 11.1.0.6.

What is STANDARD and DBMS_STANDARD identifier data?

STANDARD and DBMS_STANDARD identifier data can be used to track the usage of identifiers declared in STANDARD or DBMS_STANDARD in your code base.  These identifiers include base type such as VARCHAR2 and NUMBER and procedures such as raise_application_error().

Do I need STANDARD and DBMS_STANDARD identifier data?

It is very possible that you do not need STANDARD or DBMS_STANDARD PL/Scope identifier data and therefore do not need to execute the following procedure.  If you don't need to know where identifiers such as VARCHAR2 or NUMBER are referenced in your code base, then you probably don't need STANDARD or DBMS_STANDARD identifier data.  However, if you have a specific use case which requires searching for STANDARD or DBMS_STANDARD identifiers, such as finding all references to BINARY_INTEGER in your code base so that you can replace them with PLS_INTEGER, you will require STANDARD and DBMS_STANDARD identifier data and may need to execute the procedure.

It cannot be stressed enough that STANDARD and DBMS_STANDARD identifier data is NOT necessary to use PL/Scope.  Do not perform the following procedure unless you know that you have a need to search for identifiers declared in STANDARD or DBMS_STANDARD.   If your particular application of PL/Scope only requires PL/Scope identifier data for identifiers declared in your code base, you can follow the instructions given in the PL/Scope documentation to collect that identifier data.

Do I already have STANDARD and DBMS_STANDARD identifier data in my database?

It is possible that your database already contains STANDARD and DBMS_STANDARD PL/Scope identifier data.  A newly created Oracle 11.1.0.7 database, or a database that was upgraded to 11.1.0.7 from Oracle 10.2 or below will already contain STANDARD and DBMS_STANDARD identifier data.  However, users of Oracle 11.1.0.6 databases or Oracle 11.1.0.7 databases that were upgraded from 11.1.0.6 may not contain STANDARD and DBMS_STANDARD identifier data.

To determine if your database already contains PL/Scope STANDARD and DBMS_STANDARD identifier data, execute the following query:

SQL> SELECT UNIQUE OBJECT_NAME FROM ALL_IDENTIFIERS

2 WHERE OBJECT_NAME IN ('STANDARD', 'DBMS_STANDARD')

3 AND OWNER='SYS' ;

OBJECT_NAME

------------------------------

DBMS_STANDARD

STANDARD

2 rows selected.

If 2 rows are selected, then your database already contains STANDARD and DBMS_STANDARD identifier data. You do not need to execute the procedure and may skip reading the rest of this page.

If no rows are selected, then your database does not contain PL/Scope identifier data for STANDARD and DBMS_STANDARD and you will have to execute the following procedure in order to collect that data.

Ok, I have determined that I do not already have STANDARD and DBMS_STANDARD PL/Scope identifier data and need it. What now?

If you decide that you would like STANDARD and DBMS_STANDARD identifier data, you can perform the following procedure to collect the data.

Note that a DBA or other user with SYSDBA permissions must perform this procedure and that all PL/SQL units in the database will be invalidated and recompiled.  For development environments, the one-time cost of doing this is unlikely to be a problem.  If you have a production environment, please be absolutely certain that you require STANDARD and DBMS_STANDARD PL/Scope identifier data and that you understand the consequences of performing the procedure before continuing.

  1. Connect to the database, shut it down, and then start it in UPGRADE mode:

SQL> CONNECT / AS SYSDBA;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP PFILE= parameter_initialization_file UPGRADE;

  1. STANDARD and DBMS_STANDARD are recompiled in utlirp.sql.  Therefore, the following alter session will result in collection of PL/Scope identifier data for these packages

SQL> ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

  1. Invalidate and recompile the database:

SQL> @?/rdbms/admin/utlirp.sql

Now all PL/SQL objects in the database are invalid except STANDARD and DBMS_STANDARD, which were recompiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL'.

  1. Shut down the database, and then start it in NORMAL mode:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP PFILE= parameter_initialization_file;

  1. For any remaining invalid PL/SQL objects, do one of the following:
    • Allow them to be recompiled automatically, as they are referenced
    • Run the script utlrp.sql to force the recompilation of all invalid PL/SQL objects using the following procedure:

a) Connect to the database as SYS:

             SQL> CONNECT / AS SYS;

b) Run  utlrp.sql:

SQL> @?/rdbms/admin/utlrp.sql

If the script gives you any instructions, follow them, and then run the script again.

If the script terminates abnormally without giving any instructions, run it again.