12.2: PL/Scope Now Analyzes SQL Statements!

View All Scripts Login to Run Script

Script Name 12.2: PL/Scope Now Analyzes SQL Statements!
Description PL/Scope is a powerful code analysis tool for PL/SQL program units, first introduced in Oracle Database 11g. Now with 12.2, that analysis includes the SQL statements found inside your PL/SQL code base. This significant enhancement now allows developers to perform comprehensive impact analysis of changes to tables, such as changing a column's name. Doc link: http://docs.oracle.com/database/122/ADFNS/plscope.htm
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday January 19, 2017

Statement 1
In 12.2, there is now a new "statements:all" option which you must include to turn on gathering of information about SQL statements.
Turn on PL/Scope
```
ALTER SESSION SET plscope_settings='identifiers:all, statements:all'
```
```
Statement processed.
```
Statement 2
The next several statements create database objects (tables and PL/SQL program units that reference those tables), to give us some data to analyze.
OK Let's Create Some DB Objects!
```
CREATE TABLE species_locations 
( 
   id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
   location   VARCHAR2 (100) UNIQUE 
)
```
```
Table created.
```

Statement 3

CREATE TABLE endangered_species
(
   id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name          VARCHAR2 (100) UNIQUE,
   location_id   NUMBER    REFERENCES species_locations (id)
)

Table created.

Statement 4

CREATE TABLE research_locations
(
   id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name          VARCHAR2 (100) UNIQUE,
   location_id   NUMBER    REFERENCES species_locations (id)
)

Table created.

Statement 5

CREATE OR REPLACE PACKAGE species_mgr
   AUTHID DEFINER
IS
   FUNCTION location_id_from_name (
      location_in   IN species_locations.location%TYPE)
      RETURN species_locations.id%TYPE;
END;

Package created.

Statement 6

CREATE OR REPLACE PACKAGE BODY species_mgr
IS
   FUNCTION location_id_from_name (
      location_in   IN species_locations.location%TYPE)
      RETURN species_locations.id%TYPE
   IS
      l_return   species_locations.id%TYPE;
   BEGIN
      SELECT sl.id
        INTO l_return
        FROM species_locations sl
       WHERE sl.location = location_id_from_name.location_in;

      RETURN l_return;
   END;
END;

Package Body created.

Statement 7

OK, it's true that you don't need any data in the tables to analyze dependencies and references. But I had fun looking up the information! Except that they are endangered....

Add Some Data

BEGIN 
   /* Amazon Data */ 
 
   INSERT INTO species_locations (location) 
        VALUES ('Amazon'); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Black Spider Monkey', 
                     species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO endangered_species (name, location_id) 
        VALUES ('Sloth', species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Amazon River Dolphin', 
                     species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Poison Dart Frog', 
                     species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO endangered_species (name, location_id) 
        VALUES ('Macaw', species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO endangered_species (name, location_id) 
        VALUES ('Jaguar', species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO research_locations (name, location_id) 
           VALUES ( 
                     'Smithsonian Tropical Research Institute', 
                     species_mgr.location_id_from_name ('Amazon')); 
 
   INSERT INTO research_locations (name, location_id) 
        VALUES ('IBAMA', species_mgr.location_id_from_name ('Amazon')); 
 
   /* Galapagos Data */ 
 
   INSERT INTO species_locations (location) 
        VALUES ('Galapagos'); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Sea Turtle', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Leatherback Turtle', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO endangered_species (name, location_id) 
        VALUES ('SEI Whale', species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Green Turtle', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Giant Tortoise', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO endangered_species (name, location_id) 
           VALUES ( 
                     'Galapagos Penguin', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO research_locations (name, location_id) 
           VALUES ( 
                     'Charles Darwin Foundation', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   INSERT INTO research_locations (name, location_id) 
           VALUES ( 
                     'Galapagos Science Center', 
                     species_mgr.location_id_from_name ('Galapagos')); 
 
   COMMIT; 
END;

1 row(s) inserted.

Statement 8

CREATE OR REPLACE PROCEDURE add_species (NAME_IN       IN VARCHAR2,
                                         location_in   IN VARCHAR2)
   AUTHID DEFINER
IS
   l_info   VARCHAR2 (32767);
BEGIN
   INSERT INTO endangered_species (name, location_id)
        VALUES (NAME_IN, species_mgr.location_id_from_name (location_in));

   SELECT s.id || '-' || s.name || '-' || l.location
     INTO l_info
     FROM endangered_species s, species_locations l
    WHERE s.location_id = l.id;

   DBMS_OUTPUT.put_line ('Inserted ' || l_info);
END;

Procedure created.

Statement 9

CREATE OR REPLACE PROCEDURE show_location_info (
   location_id_in   IN species_locations.id%TYPE)
   AUTHID DEFINER
IS
BEGIN
   FOR rec IN (  SELECT r.location_id, r.name
                   FROM research_locations r
                  WHERE r.location_id = show_location_info.location_id_in
               ORDER BY r.name)
   LOOP
      DBMS_OUTPUT.put_line (rec.location_id || '-' || rec.name);
   END LOOP;
END;

Procedure created.

Statement 10

The PL/SQL team cleverly designed the new view so that it can be used "side by side" with the previous ALL/USER_IDENTIFIERS. In particular, the usage_id values are distinct across both tables. This query unions together identifier and statement data to give us a complete look at the hierarchy of identifier and statement usage.

Merged View of Identifiers and Statements

WITH one_obj_name AS (SELECT 'ADD_SPECIES' object_name FROM DUAL)  
    SELECT plscope_type,  
           usage_id,  
           usage_context_id,  
           LPAD (' ', 2 * (LEVEL - 1)) || usage || ' ' || name usages,  
           line,  
           col  
      FROM (SELECT 'ID' plscope_type,  
                   ai.object_name,  
                   ai.usage usage,  
                   ai.usage_id,  
                   ai.usage_context_id,  
                   ai.TYPE || ' ' || ai.name name,  
                   ai.line,  
                   ai.col  
              FROM user_identifiers ai, one_obj_name  
             WHERE ai.object_name = one_obj_name.object_name  
            UNION ALL  
            SELECT 'ST',  
                   st.object_name,  
                   st.TYPE,  
                   st.usage_id,  
                   st.usage_context_id,  
                   'STATEMENT',  
                   st.line,  
                   st.col  
              FROM user_statements st, one_obj_name  
             WHERE st.object_name = one_obj_name.object_name)  
START WITH usage_context_id = 0  
CONNECT BY PRIOR usage_id = usage_context_id


PLSCOPE_TYPE USAGE_ID USAGE_CONTEXT_ID USAGES LINE COL
ID 1 0 DECLARATION PROCEDURE ADD_SPECIES 1 11
ID 2 1   DEFINITION PROCEDURE ADD_SPECIES 1 11
ID 3 2     DECLARATION FORMAL IN NAME_IN 1 24
ID 4 3       REFERENCE CHARACTER DATATYPE VARCHAR2 1 41
ID 5 2     DECLARATION FORMAL IN LOCATION_IN 2 42
ID 6 5       REFERENCE CHARACTER DATATYPE VARCHAR2 2 59
ID 7 2     DECLARATION VARIABLE L_INFO 5 4
ID 8 7       REFERENCE CHARACTER DATATYPE VARCHAR2 5 13
ST 9 2     INSERT STATEMENT 7 4
ID 10 9       REFERENCE TABLE ENDANGERED_SPECIES 7 16
ID 11 9       REFERENCE FORMAL IN NAME_IN 8 17
ID 12 9       REFERENCE COLUMN LOCATION_ID 7 42
ID 13 9       REFERENCE COLUMN NAME 7 36
ID 14 9       REFERENCE PACKAGE SPECIES_MGR 8 26
ID 15 14         CALL FUNCTION LOCATION_ID_FROM_NAME 8 38
ID 16 15           REFERENCE FORMAL IN LOCATION_IN 8 61
ST 17 2     SELECT STATEMENT 10 4
ID 18 17       REFERENCE TABLE SPECIES_LOCATIONS 12 33
ID 19 17       REFERENCE TABLE ENDANGERED_SPECIES 12 11
ID 20 17       REFERENCE COLUMN ID 13 29
ID 21 17       REFERENCE COLUMN LOCATION_ID 13 13
ID 22 17       REFERENCE COLUMN LOCATION 10 45
ID 23 17       REFERENCE COLUMN NAME 10 28
ID 24 17       REFERENCE COLUMN ID 10 13
ID 25 17       ASSIGNMENT VARIABLE L_INFO 11 11

25 rows selected.

PLSCOPE_TYPE	USAGE_ID	USAGE_CONTEXT_ID	USAGES	LINE	COL
ID	1	0	DECLARATION PROCEDURE ADD_SPECIES	1	11
ID	2	1	DEFINITION PROCEDURE ADD_SPECIES	1	11
ID	3	2	DECLARATION FORMAL IN NAME_IN	1	24
ID	4	3	REFERENCE CHARACTER DATATYPE VARCHAR2	1	41
ID	5	2	DECLARATION FORMAL IN LOCATION_IN	2	42
ID	6	5	REFERENCE CHARACTER DATATYPE VARCHAR2	2	59
ID	7	2	DECLARATION VARIABLE L_INFO	5	4
ID	8	7	REFERENCE CHARACTER DATATYPE VARCHAR2	5	13
ST	9	2	INSERT STATEMENT	7	4
ID	10	9	REFERENCE TABLE ENDANGERED_SPECIES	7	16
ID	11	9	REFERENCE FORMAL IN NAME_IN	8	17
ID	12	9	REFERENCE COLUMN LOCATION_ID	7	42
ID	13	9	REFERENCE COLUMN NAME	7	36
ID	14	9	REFERENCE PACKAGE SPECIES_MGR	8	26
ID	15	14	CALL FUNCTION LOCATION_ID_FROM_NAME	8	38
ID	16	15	REFERENCE FORMAL IN LOCATION_IN	8	61
ST	17	2	SELECT STATEMENT	10	4
ID	18	17	REFERENCE TABLE SPECIES_LOCATIONS	12	33
ID	19	17	REFERENCE TABLE ENDANGERED_SPECIES	12	11
ID	20	17	REFERENCE COLUMN ID	13	29
ID	21	17	REFERENCE COLUMN LOCATION_ID	13	13
ID	22	17	REFERENCE COLUMN LOCATION	10	45
ID	23	17	REFERENCE COLUMN NAME	10	28
ID	24	17	REFERENCE COLUMN ID	10	13
ID	25	17	ASSIGNMENT VARIABLE L_INFO	11	11

Statement 11

Show all the references to columns in ADD_SPECIES, along with the table in which the column belongs.

Show All Column References

  SELECT ref_ids.object_name,  
         ref_ids.line,  
         ref_ids.name column_referenced,  
         decl_ids.object_name declared_in_table  
    FROM user_identifiers ref_ids, user_identifiers decl_ids  
   WHERE     ref_ids.object_name = 'ADD_SPECIES'  
         AND ref_ids.signature = decl_ids.signature  
         AND decl_ids.usage = 'DECLARATION'  
         AND ref_ids.TYPE = 'COLUMN'  
         AND ref_ids.usage = 'REFERENCE'  
ORDER BY line


OBJECT_NAME LINE COLUMN_REFERENCED DECLARED_IN_TABLE
ADD_SPECIES 7 NAME ENDANGERED_SPECIES
ADD_SPECIES 7 LOCATION_ID ENDANGERED_SPECIES
ADD_SPECIES 10 NAME ENDANGERED_SPECIES
ADD_SPECIES 10 LOCATION SPECIES_LOCATIONS
ADD_SPECIES 10 ID ENDANGERED_SPECIES
ADD_SPECIES 13 ID SPECIES_LOCATIONS
ADD_SPECIES 13 LOCATION_ID ENDANGERED_SPECIES

7 rows selected.

OBJECT_NAME	LINE	COLUMN_REFERENCED	DECLARED_IN_TABLE
ADD_SPECIES	7	NAME	ENDANGERED_SPECIES
ADD_SPECIES	7	LOCATION_ID	ENDANGERED_SPECIES
ADD_SPECIES	10	NAME	ENDANGERED_SPECIES
ADD_SPECIES	10	LOCATION	SPECIES_LOCATIONS
ADD_SPECIES	10	ID	ENDANGERED_SPECIES
ADD_SPECIES	13	ID	SPECIES_LOCATIONS
ADD_SPECIES	13	LOCATION_ID	ENDANGERED_SPECIES

Statement 12

Rather than hard-code a table and column names in a query, let's build a helper procedure to display using DBMS_OUTPUT.PUT_LINE all the places that a particular column is referenced in your schema. Change to ALL_* to find references across all schemas (to which you have access).

Let's Put That in a Procedure!

CREATE OR REPLACE PROCEDURE show_column_usages (table_in    IN VARCHAR2,  
                                                column_in   IN VARCHAR2)  
   AUTHID DEFINER  
IS  
BEGIN  
   DBMS_OUTPUT.put_line (  
      'References to ' || table_in || '.' || column_in);  
   DBMS_OUTPUT.put_line ('');  
  
   FOR rec  
      IN (  SELECT idt.line, idt.object_name code_unit,  
                   RTRIM (src.text, CHR (10)) text  
              FROM user_identifiers idt, user_source src  
             WHERE     idt.usage = 'REFERENCE'  
                   AND idt.signature =  
                          (SELECT idt_inner.signature  
                             FROM user_identifiers idt_inner  
                            WHERE     idt_inner.object_name =  
                                         show_column_usages.table_in  
                                  AND idt_inner.TYPE = 'COLUMN'  
                                  AND idt_inner.name =  
                                         show_column_usages.column_in  
                                  AND idt_inner.usage = 'DECLARATION')  
                   AND idt.line = src.line  
                   AND idt.object_name = src.name  
          ORDER BY code_unit, line)  
   LOOP  
      DBMS_OUTPUT.put_line (  
         'In ' || rec.code_unit || ' on ' || rec.line || ': ' || rec.text);  
   END LOOP;  
END;

Procedure created.

Statement 13

BEGIN 
   show_column_usages (table_in    => 'ENDANGERED_SPECIES', 
                       column_in   => 'LOCATION_ID'); 
END;

References to ENDANGERED_SPECIES.LOCATION_ID
In ADD_SPECIES on 7:    INSERT INTO endangered_species (name, location_id)
In ADD_SPECIES on 13:     WHERE s.location_id = l.id;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database