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