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 (18.104.22.168), 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!