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