PL/Scope in Oracle Database 12c Release 2 and Oracle SQL Developer

thatjeffsmith SQL Developer 6 Comments

Tell Others About This Story:

PL/Scope is one of my favorite features.

It answers SO many questions.

Where am I declaring this, where am I calling that, when am I referencing something? And, I can get this information automatically, without hitting the big, fat SOURCE data dictionary views.

sql developer plscope search

Where and what is my CURSOR (C1) doing?

I’ve talked about this previously

…BUT, PL/SCope didn’t help me with tracking the SQL I used in my PL/SQL.

That is, it didn’t until 12cR2.

Starting with Oracle Database 12c Release 2 (, a new view, DBA_STATEMENTS, reports on the occurrences of static SQL in PL/SQL units. It provides information about the SQL_ID, the canonical statement text, the statement type, useful statement usage attributes, its signature, and location in the PL/SQL code. Each row represents a SQL statement instance in the PL/SQL code (DOCS).

Whiz bang, indeed.

For now, SQL Developer’s Search feature doesn’t dip into the new STATEMENTS views, so you’ll need to build your own queries. But maybe you should consider a REPORT, AGAIN.

Show me ALL the SQL My Code Is Using.

And WHERE it’s used.

Let’s build a report!

sql developer report plscope

Master – Detail report.

So I’m asking to GET all of the statements.

FROM all_statements
WHERE text LIKE '%' || :plsql || '%'
ORDER BY sql_id

So I’m wrapping the bind with wildcards – we’re searching source code, so this will be handy. If my user just hits ENTER, it’ll bring back everything.

interactive reports – prompting the user is EASY

Now we have our results. This SQL_ID is being used in two different PL/SQL objects.

Click on the hyperlink to open the object.

The funny blue hyperlink-y looking text – click on that, and it will open the object! – in a report is using a ‘trick.’

     ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' OBJECT,
FROM all_statements WHERE SQL_ID = :SQL_ID

Note that I’m not showing ALL the columns from the Scope dictionary view. It will tell you if your code is has a ‘FOR UPDATE’ clause for instance.

Need 12cR2 to play around with?

Don’t forget our image!

oracle virtualbox image

The image is up and running, complete with sample data for you to play with.

Tell Others About This Story:

Comments 6

  1. Thanks for the information.
    PL/Scope can help you answer questions such as :

    >>Where and how a column x in table y is used in the PL/SQL code?

    >>Is the SQL in my application PL/SQL code compatible with TimesTen?

    >>What are the constants, variables and exceptions in my application that are declared but never used?

    >>Is my code at risk for SQL injection?

    >>What are the SQL statements with an optimizer hint coded in the application?

    >>Which SQL has a BULK COLLECT clause ? Where is the SQL called from ?


  2. I maybe found a bug.
    I unchecked ALL_OBJECT_TYPES and just left PACKAGES in. The search then no longer found the tables where my text was in the name (I was confused by the partitioned tables being listed for every partition and not only for the main table name, so I tested unchecking).
    Then I checked the whole category in again, but my TABLE hits did not show up again, neither the VIEWS I had hits before.
    I then tested on another connection, there the TABLE hits and VIEWS show up. I rebooted the SQL Developer, but no TABLE or VIEW hits any more on my first connection where I checked them off and then on again. And also nothing happens after just closing the connection and reestablishing it.
    This seems to be a bug, or do I do anything wrong?
    I would also wish, that I could configure if I want all partitions of a table listed or only the table itself (I would prefer the second since the fields are named all the same on any partition of a given table).

    1. thatjeffsmith Post


      I checked package and package body only and was able to find my code on the first try.

      I wasn’t able to see what you’re seeing in either 4.1.3 or our latest internal 4.2 build.

      I don’t know what you mean by partitions, if I search for a partitioned table, and it’s found, it’s only listed the one time.

      Then again, i’m not sure we’re talking about the same thing – you’re talking about object search and this post was about PL/Scope and you’re not doing Scope searches.

      1. Yes, no problem with finding code lines. Not finding the code was the problem, but finding the tables that contain the column names after unchecking tables and checking them on again on the preference boxes of the search (I wrote: The search then no longer found the tables where my text was in the name – it seems that was not clear enough?).
        But today, after I rebooted my computer, the search works again as expected. No idea what happened yesterday and why I got no more table lists when searching for column names.

        But thanks for replying,
        I appreciate your hard work.

  3. “without hitting the bit, fat SOURCE data dictionary views.” …

    I am a ‘BIT’ confused…

    Did you mean ‘big’?

    BTW, thank you for all you do, Jeff. I am actually going to DL and install 12cR2 to mess around with on my test server today. You give me quite a ‘bit’ of useful advice!

    1. thatjeffsmith Post

      Ha. Yes.


      As in ALL_SOURCE has every single line and byte of your code in it, and searching it is expensive. Searching the PL/Scope data dictionary views is much nicer – and has a lot of metadata there for you to mine already. I’ll update the post now 🙂

Leave a Reply

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