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 with Comments Off | Tags: 11g, pl/sql, plscopeYet Another Oracle Social Network in the Works
In addition to Oracle Mix, Oracle Wiki and Oracle Community, OAUG will be launching yet another Oracle related social network called the Knowledge Factory.
The Knowledge Factory will provide a platform for users to exchange ideas, experiences, and expertise within the Oracle Applications member community. It will feature user profiles with pictures and biographies, blogs, forums and wiki. Sounds familiar?
Sources:
Filed in Oracle with 7 Comments | Tags: oaug, oraclecommunity, socialnetwork, wikiOracle SQL and PL/SQL Bad Practices Document
The document below contains patterns of bad SQL and PL/SQL code that Gojko Adzic has repeatedly found in various applications and databases. Some of the bad practices include:
- Use of WHEN OTHERS in exception handling.
- Embedding complex SQL inside PL/SQL code.
- Poor PL/SQL error handling.
- Hardcoding the size of PL/SQL variables.
- Not using bind variables.
- Storing ROWIDs for later reference.
- Storing an empty LOB instead of NULL.
- Use of COMMIT or ROLLBACK inside stored procedures or functions.
Use of magic numbers and strings instead of NULL.
