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

PL/Scope in Oracle Database 11g

This new Oracle Database 11g feature is PL/Scope:

PL/Scope is a compiler-driven tool that collects data about user-defined identifiers from PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code. PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

According to Dan Morgan, If you execute the following in Oracle 11g you will end up with a corrupt database (I have not tested it myself): Compiling STANDARD should be done while the database is in UPGRADE mode, more info.

conn / as sysdba



But the above is Oracle’s recommendation; According to the documentation: Each DATATYPE is a base type declared in package STANDARD. In order to collect and view these identifiers, package STANDARD must be compiled with PLSCOPE_SETTINGS=’IDENTIFIERS:ALL’.

Dan goes on to say: “I can not warn everyone strongly enough to ignore this recommendation unless you are quite prepared to perform a complete re-installation.Compiling STANDARD should be done while the database is in UPGRADE mode, more info.

By the way, it was good to see Dan getting into blogging. But his blog lacks many basic and essential features that differentiate a blog from a regular website. Features like RSS feeds, commenting, permalinks, archives, search… are all missing. I wonder why he did not pick a “real” blogging platform.

Update: Here is the answer from Oracle.

Filed in Oracle, Tips on 10 Sep 07

Reader's Comments

  1. |

    Maybe Daniel Morgan is just experimenting with the format.

    Although he calls it a Blog, it is merely a Web page with chronologically ordered content created by, err, FrontPage.

  2. |

    Well I think that’s precisely the sort of spartan approach to features we shhould expect from a man who names his blog after vi. 🙂

    Cheers, APC

  3. |


    It works with no problems without doing the addition compile of the standard package:



  4. |

    @Andy C: Yes, I agree. I saw the following in the source code of his “blog’s” web page: meta name=”GENERATOR” content=”Microsoft FrontPage 4.0″.

    @APC: Indeed. Notice that he made it explicit it was not named “Oracle Notepad”.

    @Tim: I’m totally working from the documentation since I have yet to install 11g and start testing around.

    The documentation says: “In order to collect and view these identifiers, package STANDARD must be compiled with PLSCOPE_SETTINGS=’IDENTIFIERS:ALL'”. Now, what are “these identifiers”? They are:


    If collected, they will show up in the Type column in the the %_IDENTIFIERS view.

    So, my understanding is that if you do not re-compile the STANDARD package, you will not be able to collect the above identifiers, however, PL/Scope will still work and the other types of identifiers will be collected just fine, again, according to the documentation.

    Have you tried to re-compile the STANDARD package with plscope_settings=’IDENTIFIERS:ALL’? If you do, please do not blame me or Dan if your database goes crazy :).

  5. |

    Well , I have used Snapshot standby database to experiment above mentioned steps

    I tried to re-compile the STANDARD package with plscope_settings=’IDENIFIERS:ALL’ , around 7000+ objects got invalid

  6. |

    Anuraq: if you did not spell IDENTIFIERS but IDENIFIERS this makes sense…

  7. |

    I think it’s cheap to comment on how Daniel Morgan creates his ‘blog’. As with his library pages, functionality is more important than form. And I can understand why he has not enabled features like posting comments, knowing the comment history of newsgroups. Just hope his blog will show up in Google Blog Alerts….

    What’s in a blog?

  8. |


    I would suggest anything that requires the standard package to be recompiled is probably cause for a rerun of the catproc.sql script.

    I tried the compile-only method and it invalidated over 7000 objects, most of which wouldn’t recompile when I ran the utlrp.sql script.

    The following seemed to work fine:

    CONN / AS SYSDBA ALTER SESSION SET plscope_settings=’IDENTIFIERS:ALL’; @?/rdbms/admin/catproc.sql @?/rdbms/admin/utlrp.sql

    At the end of this I had no invalid objects and the DB seemed fine. Even so, I’m switching back to a snapshot just in case. 🙂



  9. |

    Whoops. Should have used catalog.sql, not catproc.sql! I’m trying again now. 🙂



  10. |

    Thanks guys for trying to invalidate 7000 objects with one command 🙂

    I believe that the documentation needs to be modified anyways. Dan mentioned that he already contacted Oracle about this.

    Shakespeare (very nice name by the way), I agree with you that functionality is more important than form, but this does not mean that form is not important.

    To me, a blog without an RSS feed is not a blog. I just do not want to visit any “blog” every day just to see if the author has published something new.

  11. |


    The instance is still screwed using the catalog.sql approach. Looks like doing anything with the standard package is a bad idea.

    I guess we will have to wait and see what Oracle say.



  12. |

    I’m not seeing any bug filing on this on the Oracle side. Does anyone know if there an SR filed on this database corruption? If this is actually a total show-stopper database corruption issue we need to get word out about this ASAP. If so, I will try to get you to the right people at Oracle.

  13. |

    Duplicated comment. This one has the right email.

  14. |

    Chris, I am not aware of any SR filed on this. In addition to what Dan posted on his site, I am trying to get the word out by blogging about it here as well. I have also posted a link to this post in the Oracle ACE Director Database Private Forum. So now you know.

    Moreover, Dan says that he is working on getting an answer from Oracle about this. I have emailed Dan to ask him if he got a reply from Oracle, but I have not heard back from him yet.

  15. |

    Here is the answer from Oracle.