8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
PL/Scope Enhancements in Oracle Database 12c Release 2 (12.2)
PL/Scope is a tool that gathers information about user defined identifiers in PL/SQL code at compile time. Oracle Database 12c Release 2 (12.2) enhances the information captured by PL/Scope.
Most users will view the PL/Scope data via a PL/SQL IDE, such as SQL Developer.
Related articles.
Control PL/Scope Data Collection
Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of "IDENTIFIERS:NONE". Unlike the previous releases, in Oracle 12.2 the PLSCOPE_SETTINGS parameter can accept separate settings for IDENTIFIERS and STATEMENTS.
# Pre-12.2
IDENTIFIERS : { ALL | NONE }
# 12.2
IDENTIFIERS : { ALL | NONE | PUBLIC | SQL | PLSQL }
STATEMENTS : { ALL | NONE }
If IDENTIFIERS is set to a value other than NONE and STATEMENTS is not set, it is the equivalent of STATEMENTS:ALL. This means the following are equivalent.
ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'; ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL, STATEMENTS:ALL';
The data is stored in the SYSAUX tablespace, so the current space used for PL/Scope data can be displayed with the following query.
SELECT space_usage_kbytes
FROM v$sysaux_occupants
WHERE occupant_name = 'PL/SCOPE';
SPACE_USAGE_KBYTES
------------------
384
1 row selected.
SQL>
It's probably better to collect identifier information for specific items of interest, rather than doing it for all code, as this will waste space in the SYSAUX tablespace.
Collect PL/Scope Data
Data collection is performed for all objects compiled in the session after the flag is set, so we must now create an object to gather some data.
CREATE OR REPLACE PROCEDURE test_plscope (p_in IN NUMBER) AS
CURSOR c_cursor IS
SELECT dummy
FROM dual;
l_num NUMBER;
l_str VARCHAR2(32767);
BEGIN
l_str := p_in;
OPEN c_cursor;
FETCH c_cursor
INTO l_str;
CLOSE c_cursor;
SELECT dummy
INTO l_str
FROM dual;
l_str := l_str || l_str;
END;
/
Display PL/Scope Data
The PL/Scope identifier data is available from the {DBA|ALL|USER}_IDENTIFIERS views. The following query displays data gathered during the compilation of the test_plscope procedure.
SET LINESIZE 100
COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name,
type,
usage,
usage_id,
line,
col
FROM user_identifiers
WHERE object_name = 'TEST_PLSCOPE'
AND object_type = 'PROCEDURE'
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
NUMBER NUMBER DATATYPE REFERENCE 4 1 35
C_CURSOR CURSOR DECLARATION 5 2 10
L_NUM VARIABLE DECLARATION 9 6 3
NUMBER NUMBER DATATYPE REFERENCE 10 6 9
L_STR VARIABLE DECLARATION 11 7 3
VARCHAR2 CHARACTER DATATYPE REFERENCE 12 7 9
L_STR VARIABLE ASSIGNMENT 13 9 3
P_IN FORMAL IN REFERENCE 14 9 12
L_STR VARIABLE ASSIGNMENT 26 20 3
L_STR VARIABLE REFERENCE 27 20 12
L_STR VARIABLE REFERENCE 28 20 21
SQL>
The PL/Scope statement data is available from the {DBA|ALL|USER}_STATEMENTS views. The following query displays data gathered during the compilation of the test_plscope procedure.
COLUMN full_text FORMAT A50
SELECT line,
col,
type,
full_text
FROM user_statements
WHERE object_name = 'TEST_PLSCOPE'
AND object_type = 'PROCEDURE'
ORDER BY 1;
LINE COL TYPE FULL_TEXT
---------- ---------- ----------------- --------------------------------------------------
3 5 SELECT SELECT DUMMY FROM DUAL
11 3 OPEN
12 3 FETCH
14 3 CLOSE
16 3 SELECT SELECT DUMMY FROM DUAL
SQL>
For more information see:
Hope this helps. Regards Tim...