DBA Hub

📋Steps in this guide1/3

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.

oracle 12cconfigurationintermediate
by OracleDba
15 views
1

Control PL/Scope Data Collection

Collection of PL/Scope data is controlled by the parameter, which has a default setting of " ". Unlike the previous releases, in Oracle 12.2 the parameter can accept separate settings for and . If is set to a value other than and is not set, it is the equivalent of . This means the following are equivalent. The data is stored in the SYSAUX tablespace, so the current space used for PL/Scope data can be displayed with the following query. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# Pre-12.2
IDENTIFIERS : { ALL | NONE }

# 12.2
IDENTIFIERS : { ALL | NONE | PUBLIC | SQL | PLSQL }
STATEMENTS  : { ALL | NONE }

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL, STATEMENTS:ALL';

SELECT space_usage_kbytes
FROM   v$sysaux_occupants
WHERE  occupant_name = 'PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
               384

1 row selected.

SQL>
2

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;
/
3

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 procedure. 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 procedure. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!