News, views, tips and tricks on Oracle and other fun stuff

PL/Scope in Oracle Database 11g – Revisited

Oracle Database 11g introduced a new feature called PL/Scope. A while back, I wrote about Dan Morgan’s experience when he compiled the package STANDARD for PL/Scope. I also wrote about Oracle’s answer, in which they said “…the reason to compile STANDARD would be to make its identifiers available in the new DBA_Identifiers view family (see PL/Scope). Our script to do this sadly missed the release. We’re about to post it on OTN.”

In fact, the PL/Scope documentation mentions a utlirplscope.sql script:

A database that has been upgraded from a release of Oracle Database that did not yet support PL/Scope will have no PL/Scope metadata—nor will a new Oracle Database 11g environment that has been ordinarily created. The DBA can rectify this by running the utlirplscope.sql script.

In an email, Bryn Llewellyn, Oracle’s PL/SQL Product Manager, was kind enough to point me to this page on OTN:

On the Subject of the utlirplscope.sql script

It turns out that a script is not needed after all:

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.

I suggest you read this OTN piece if you want to know the answers to the following questions:

  • What was utlirplscope.sql?
  • What is STANDARD and DBMS_STANDARD identifier data?
  • Do I need STANDARD and DBMS_STANDARD identifier data?
  • Do I already have STANDARD and DBMS_STANDARD identifier data in my database?
  • Ok, I have determined that I do not already have STANDARD and DBMS_STANDARD PL/Scope identifier data and need it. What now?

Important notes to take from this:

  • Not everyone needs STANDARD and DBMS_STANDARD identifier data.
  • When needed, it’s going to be mostly in development environments.
  • Compiling STANDARD and DBMS_STANDARD using utlirp.sql should be done while the database is in UPGRADE mode.

In conclusion, I agree with Bryn that PL/Scope is a fine feature. Just get your DBA to read the OTN reference above if your use case would benefit from having STANDARD and DBMS_STANDARD identifier data.

Filed in Oracle on 25 Mar 08 | Tags: , ,

Comments are closed.