PL/Scope Support

thatjeffsmith SQL Developer 9 Comments

Tell Others About This Story:
I forgot to blog this!

I forgot to blog this!

The PL/SQL team is always reminding me to talk about PL/Scope. And I got to the point where I needed to remind myself, so I added this slide in all of my PL/SQL themed talks. Yet, I have apparently forgotten to blog about it. Oops.

[Docs] PL/Scope is a compiler-driven tool that collects data about identifiers in 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 lets you develop powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

PL/Scope is intended for application developers, and is usually used in the environment of a development database.

So, the basic gist is, instead of searching your PL/SQL source code looking for every occurrence of ‘X’ – and having to parse the source code yourself, at compile time, PL/Scope will find your identifiers and put them in an easy place for you to query.

So, when someone decides to change what ‘X’ is, it’s very easy for you to see the impact of this change throughout all of your PL/SQL.

The other benefit is that it’s just plain faster to search the PL/Scope views than it is to go try to brute-force your way though ALL_SOURCE.

Of course, SQL Developer supports this.

Step One, make sure you’re asking for the PL/Scope information to be collected.

It's an 'on' or 'off' type of feature.

It’s an ‘on’ or ‘off’ type of feature.

When you connect, we’ll enable this for your session so that when you compile, the PL/Scope info will be collected.

Step Two, how to go see the data.


Find DB Objects, or the Object Search.

There's a lot going on here, so please bear with me...

There’s a lot going on here, so please bear with me…

  • open the search
  • set your search string
  • set your search depth – all schemas, your schema, a few schemas
  • set your search type
  • hit go
  • see results
  • click on result to open code editor and go to line:curpos

Note that there are MANY types of identifiers. The PL/SQL engine knows about ALL of them. I’ve searched across all types, but I could have easily said, Cursors only please.

Pretty cool, right?

Pretty cool, right?

Tell Others About This Story:

Comments 9

  1. Hi Jeff,

    in PL/SQL Developer, these reports are added as HINTS whenever you compile your source. They’re displayed along with the warnings and there’s a small option window where you can select various reports to be included as hints. That’s a nice solution and maybe a future change request for SQL Developer?

    Best regards,

  2. It looks pretty good, but doesn’t seem to work properly in Version If I tick only CURSOR, I get references in comments, parameter values, table and variable names etc.

    Maybe I need to download an update.

    1. thatjeffsmith Post

      It’s working for me. For example, I can tell it to only look into variables, and we’re adding this predicate to the query on all_identifiers

      AND TYPE in (‘VARIABLE’)

      View > Log > Statements will show you the query the object search feature is using to bring back your results

    1. thatjeffsmith Post
    1. thatjeffsmith Post
  3. I didn’t know this existed, really cool.

    I would only improve it by including a small preview of the line where this is used.

    Lets say we have two packages – p1 and p2 – both with a function named ABC.
    If I search for a function called ABC I get results where the functions for both packages are used.
    If I search for p1.ABC I only get the invocations inside p1 for some reason.

    Having a preview would help in this situation.
    Other than that, fantastic tool!

Leave a Reply

Your email address will not be published. Required fields are marked *