DISQUS

DISQUS Hello! Eddie Awad’s Blog is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Eddie Awad’s Blog

News, views, tips and tricks on Oracle and other fun stuff
Jump to original thread »
Author

PL/Scope in Oracle Database 11g

Started by Eddie Awad · 8 months ago

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 inform ... Continue reading »

15 comments

  • Andy C

    1 year ago

    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.

    reply
  • APC

    1 year ago

    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

    reply
  • Tim Hall

    1 year ago

    Hi.


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


    http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#plscope


    Cheers


    Tim...

    reply
  • Eddie Awad

    1 year ago

    @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:


    BFILE DATATYPEBLOB DATATYPEBOOLEAN DATATYPECHARACTER DATATYPECLOB DATATYPEDATE DATATYPEINTERVAL DATATYPENUMBER DATATYPETIME DATATYPETIMESTAMP DATATYPE


    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 :).

    reply
  • Anurag

    1 year ago

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


    http://www.psoug.org/reference/plscope.html


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

    reply
  • Shakespeare

    1 year ago

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

    reply
  • Shakespeare

    1 year ago

    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?

    reply
  • Tim Hall

    1 year ago

    Hi.


    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. :)


    Cheers


    Tim...

    reply
  • Tim Hall

    1 year ago

    Whoops. Should have used catalog.sql, not catproc.sql! I'm trying again now. :)


    Cheers


    Tim...

    reply
  • Eddie Awad

    1 year ago

    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.

    reply
  • Tim Hall

    1 year ago

    Hi.


    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.


    Cheers


    Tim...

    reply
  • Chris Gait

    1 year ago

    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.

    reply
  • Chris Gait

    1 year ago

    Duplicated comment. This one has the right email.

    reply
  • Eddie Awad

    1 year ago

    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.

    reply
  • Eddie Awad

    1 year ago

    Here is the answer from Oracle.

    reply

Add New Comment

Returning? Login