Hi All!
I decided to make a serie of posts with really quick notes about some really awesome features we simply don’t use. Today’s one is about PL/Scope.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
PL/Scope
PL/Scope is a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of “IDENTIFIERS:NONE”. To enable collection, switch value to “IDENTIFIERS:ALL”. The data is stored in the SYSAUX tablespace, this space needs to be well planned and managed..
The PL/Scope data is available from the %_IDENTIFIERS views. The following query displays data gathered during the compilation of a test procedure:
COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name, type, usage, usage_id, line, col
FROM user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;
NAME TYPE USAGE USAGE_ID LINE COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
TEST_PLSCOPE PROCEDURE DECLARATION 1 1 11
TEST_PLSCOPE PROCEDURE DEFINITION 2 1 11
P_IN FORMAL IN DECLARATION 3 1 25
L_VAR VARIABLE DECLARATION 4 2 3
L_VAR VARIABLE ASSIGNMENT 5 4 3
P_IN FORMAL IN REFERENCE 6 4 12
L_VAR VARIABLE ASSIGNMENT 7 6 3
L_VAR VARIABLE REFERENCE 8 6 12
8 rows selected.
(this script was taken from here)
Some additional information about it: The documentation states that some identifiers will not listed unless the STANDARD package is recompiled after the PLSCOPE_SETTINGS parameter is set properly. However there is some opening community discussions about invalidation of basic procedures not being revalidated again, even on use of urlrp procedure. This way, we don’t recommend to use it directly on Production environment without validating before in an equivalent environment and taking all caution measures as possible.
Hope you enjoy it. Cheers!