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.
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 (12.2.0.1), 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.
If you’re on a newer version of SQL Developer, we can use the Search feature to go against the Statements view.
Searching PL/Scope Statements in SQL Developer
Open the search panel – binoculars button on the main toolbar – and select your 12cR2 and higher database connection.
Decide what schemas you want to search – by default it will just search your connection schema.
Then check ‘All Statements’ under Code, enter your search string (we’ll automatically wild card it for you) – and hit the ‘Go’ button.
Click on a search result to open said PL/SQL object.
Never used the Search Before?
Here’s a quick video:
Older versions of SQL Developer?
Upgrade! And if you can’t, here’s a report I wrote awhile back before we enhanced the search feature. You can see that there’s quite a bit of useful information PL/Scope keeps on your SQL statements that our search feature doesn’t expose.
So I’m asking to GET all of the statements.
SELECT DISTINCT SQL_ID, TEXT 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.
Now we have our results. This SQL_ID is being used in two different PL/SQL objects.
The funny blue hyperlink-y looking text – click on that, and it will open the object! – in a report is using a ‘trick.’
SELECT TYPE, 'SQLDEV:LINK:' ||owner ||':' ||object_type ||':' ||OBJECT_NAME ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' OBJECT, OBJECT_TYPE, USAGE_ID, LINE, COL, has_hint 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!
7 Comments
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.
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 ?
Thanks
Sarahjohn
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).
Maybe?
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.
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.
“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!
Ha. Yes.
BIG.
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 🙂