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.
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!
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!