12.2: Use PL/Scope to find SQL statements with user-defined function calls

View All Scripts Login to Run Script

Script Name 12.2: Use PL/Scope to find SQL statements with user-defined function calls
Description When a SQL statement executes a user-defined function, your users pay the price of a context switch, which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues. Fortunately, you can use PL/Scope in 12.2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Friday June 02, 2017

Statement 1

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'

Statement processed.

Statement 2

CREATE TABLE my_data (n NUMBER)

Table created.

Statement 3

CREATE OR REPLACE FUNCTION my_function1
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;

Function created.

Statement 4

CREATE OR REPLACE FUNCTION my_function2
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;

Function created.

Statement 5

CREATE OR REPLACE PROCEDURE my_procedure (n_in IN NUMBER)
   AUTHID DEFINER
IS
   l_my_data   my_data%ROWTYPE;
BEGIN
   SELECT my_function1 ()
     INTO l_my_data
     FROM my_data
    WHERE     n = n_in
          AND my_function2 () = 0
          AND n = (SELECT my_function1 () FROM DUAL);

   SELECT COUNT (*)
     INTO l_my_data
     FROM my_data
    WHERE n = n_in;

   UPDATE my_data
      SET n = my_function2 ()
    WHERE n = n_in;
END;

Procedure created.

Statement 6

This query unions together rows from ALL_IDENTIFIERS and ALL_STATEMENTS to provide a complete picture of your program unit. Note: in LiveSQL, we can only use the USER* views.

Show All Identifiers and Statements - ALL_* Version

WITH one_obj_name AS (SELECT USER owner, 'MY_PROCEDURE' object_name FROM DUAL) 
    SELECT plscope_type, 
           usage_id, 
           usage_context_id, 
           LPAD (' ', 2 * (LEVEL - 1)) || usage || ' ' || name usages, 
           line, 
           col, 
           signature 
      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, 
                   signature 
              FROM all_identifiers ai, one_obj_name 
             WHERE ai.object_name = one_obj_name.object_name 
               AND ai.owner = one_obj_name.owner 
            UNION ALL 
            SELECT 'ST', 
                   st.object_name, 
                   st.TYPE, 
                   st.usage_id, 
                   st.usage_context_id, 
                   'STATEMENT', 
                   st.line, 
                   st.col, 
                   signature 
              FROM all_statements st, one_obj_name 
             WHERE st.object_name = one_obj_name.object_name 
               AND st.owner = one_obj_name.owner) 
START WITH usage_context_id = 0 
CONNECT BY PRIOR usage_id = usage_context_id

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 7

Show All Identifiers and Statements - USER_* Version

WITH one_obj_name AS (SELECT 'MY_PROCEDURE' object_name FROM DUAL) 
    SELECT plscope_type, 
           usage_id, 
           usage_context_id, 
           LPAD (' ', 2 * (LEVEL - 1)) || usage || ' ' || name usages, 
           line, 
           col, 
           signature 
      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, 
                   signature 
              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, 
                   signature 
              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 SIGNATURE
ID 1 0 DECLARATION PROCEDURE MY_PROCEDURE 1 11 C29C9DEA9F87581C31F0FBB613F51CA2
ID 2 1   DEFINITION PROCEDURE MY_PROCEDURE 1 11 C29C9DEA9F87581C31F0FBB613F51CA2
ID 3 2     DECLARATION FORMAL IN N_IN 1 25 1BB8769FD1C68B72D9CC466499AB87A6
ID 4 3       REFERENCE NUMBER DATATYPE NUMBER 1 33 47BFC756469F1D97B6C84EF73A9C5D48
ID 5 2     DECLARATION VARIABLE L_MY_DATA 4 4 D7A7636014070935F335C353F730FFE4
ID 6 5       REFERENCE TABLE MY_DATA 4 16 5C9CCDF06410B0DC5D6DD65A64825C66
ST 7 2     SELECT STATEMENT 6 4 98CE6433EDDE29CD961C8EEF00081E5D
ID 8 7       REFERENCE TABLE MY_DATA 8 11 5C9CCDF06410B0DC5D6DD65A64825C66
ID 9 7       REFERENCE COLUMN N 11 15 70FDDC755837EA004DC1ABCF31A4C03A
ID 10 7       REFERENCE FORMAL IN N_IN 9 19 1BB8769FD1C68B72D9CC466499AB87A6
ID 11 7       REFERENCE COLUMN N 9 15 70FDDC755837EA004DC1ABCF31A4C03A
ID 13 7       CALL FUNCTION MY_FUNCTION1 11 27 78D79DB78CFDFB3B3FAC4FC7B1B7EC58
ID 14 7       CALL FUNCTION MY_FUNCTION2 10 15 1BDDE903F7911D028C81D637300463AC
ID 15 7       ASSIGNMENT VARIABLE L_MY_DATA 7 11 D7A7636014070935F335C353F730FFE4
ID 16 15         CALL FUNCTION MY_FUNCTION1 6 11 78D79DB78CFDFB3B3FAC4FC7B1B7EC58
ST 17 2     SELECT STATEMENT 13 4 B096642F3B7EF7A1B25D1E8169C092B2
ID 18 17       REFERENCE TABLE MY_DATA 15 11 5C9CCDF06410B0DC5D6DD65A64825C66
ID 19 17       REFERENCE FORMAL IN N_IN 16 15 1BB8769FD1C68B72D9CC466499AB87A6
ID 20 17       REFERENCE COLUMN N 16 11 70FDDC755837EA004DC1ABCF31A4C03A
ID 21 17       ASSIGNMENT VARIABLE L_MY_DATA 14 11 D7A7636014070935F335C353F730FFE4
ST 22 2     UPDATE STATEMENT 18 4 B8C4072AB7A648257BB582FE3C1FEAAA
ID 23 22       REFERENCE TABLE MY_DATA 18 11 5C9CCDF06410B0DC5D6DD65A64825C66
ID 24 22       REFERENCE FORMAL IN N_IN 20 15 1BB8769FD1C68B72D9CC466499AB87A6
ID 25 22       REFERENCE COLUMN N 20 11 70FDDC755837EA004DC1ABCF31A4C03A
ID 26 22       REFERENCE COLUMN N 19 11 70FDDC755837EA004DC1ABCF31A4C03A
ID 27 22       CALL FUNCTION MY_FUNCTION2 19 15 1BDDE903F7911D028C81D637300463AC

26 rows selected.

PLSCOPE_TYPE	USAGE_ID	USAGE_CONTEXT_ID	USAGES	LINE	COL	SIGNATURE
ID	1	0	DECLARATION PROCEDURE MY_PROCEDURE	1	11	C29C9DEA9F87581C31F0FBB613F51CA2
ID	2	1	DEFINITION PROCEDURE MY_PROCEDURE	1	11	C29C9DEA9F87581C31F0FBB613F51CA2
ID	3	2	DECLARATION FORMAL IN N_IN	1	25	1BB8769FD1C68B72D9CC466499AB87A6
ID	4	3	REFERENCE NUMBER DATATYPE NUMBER	1	33	47BFC756469F1D97B6C84EF73A9C5D48
ID	5	2	DECLARATION VARIABLE L_MY_DATA	4	4	D7A7636014070935F335C353F730FFE4
ID	6	5	REFERENCE TABLE MY_DATA	4	16	5C9CCDF06410B0DC5D6DD65A64825C66
ST	7	2	SELECT STATEMENT	6	4	98CE6433EDDE29CD961C8EEF00081E5D
ID	8	7	REFERENCE TABLE MY_DATA	8	11	5C9CCDF06410B0DC5D6DD65A64825C66
ID	9	7	REFERENCE COLUMN N	11	15	70FDDC755837EA004DC1ABCF31A4C03A
ID	10	7	REFERENCE FORMAL IN N_IN	9	19	1BB8769FD1C68B72D9CC466499AB87A6
ID	11	7	REFERENCE COLUMN N	9	15	70FDDC755837EA004DC1ABCF31A4C03A
ID	13	7	CALL FUNCTION MY_FUNCTION1	11	27	78D79DB78CFDFB3B3FAC4FC7B1B7EC58
ID	14	7	CALL FUNCTION MY_FUNCTION2	10	15	1BDDE903F7911D028C81D637300463AC
ID	15	7	ASSIGNMENT VARIABLE L_MY_DATA	7	11	D7A7636014070935F335C353F730FFE4
ID	16	15	CALL FUNCTION MY_FUNCTION1	6	11	78D79DB78CFDFB3B3FAC4FC7B1B7EC58
ST	17	2	SELECT STATEMENT	13	4	B096642F3B7EF7A1B25D1E8169C092B2
ID	18	17	REFERENCE TABLE MY_DATA	15	11	5C9CCDF06410B0DC5D6DD65A64825C66
ID	19	17	REFERENCE FORMAL IN N_IN	16	15	1BB8769FD1C68B72D9CC466499AB87A6
ID	20	17	REFERENCE COLUMN N	16	11	70FDDC755837EA004DC1ABCF31A4C03A
ID	21	17	ASSIGNMENT VARIABLE L_MY_DATA	14	11	D7A7636014070935F335C353F730FFE4
ST	22	2	UPDATE STATEMENT	18	4	B8C4072AB7A648257BB582FE3C1FEAAA
ID	23	22	REFERENCE TABLE MY_DATA	18	11	5C9CCDF06410B0DC5D6DD65A64825C66
ID	24	22	REFERENCE FORMAL IN N_IN	20	15	1BB8769FD1C68B72D9CC466499AB87A6
ID	25	22	REFERENCE COLUMN N	20	11	70FDDC755837EA004DC1ABCF31A4C03A
ID	26	22	REFERENCE COLUMN N	19	11	70FDDC755837EA004DC1ABCF31A4C03A
ID	27	22	CALL FUNCTION MY_FUNCTION2	19	15	1BDDE903F7911D028C81D637300463AC

Statement 8

Here's the secret sauce. I use subquery refactoring (WITH clause) to create and then use some data sets: my_prog_unit - specify the program unit of interest just once; full_set - the full set of statements and identifiers; dml_statements - the SQL DML statements in the program unit. Then I find all the DML statements whose full_set tree below it contain a call to a function.

Find SQL Statements Containing Function Calls - ALL_* Version

WITH my_prog_unit AS (SELECT USER owner, 'MY_PROCEDURE' object_name FROM DUAL), 
     full_set 
     AS (SELECT ai.usage, 
                ai.usage_id, 
                ai.usage_context_id, 
                ai.TYPE, 
                ai.name 
           FROM all_identifiers ai, my_prog_unit 
          WHERE ai.object_name = my_prog_unit.object_name 
            AND ai.owner = my_prog_unit.owner 
         UNION ALL 
         SELECT st.TYPE, 
                st.usage_id, 
                st.usage_context_id, 
                'type', 
                'name' 
           FROM all_statements st, my_prog_unit 
          WHERE st.object_name = my_prog_unit.object_name 
            AND st.owner = my_prog_unit.owner), 
     dml_statements 
     AS (SELECT st.owner, st.object_name, st.line, st.usage_id, st.type 
           FROM all_statements st, my_prog_unit 
          WHERE     st.object_name = my_prog_unit.object_name 
                AND st.owner = my_prog_unit.owner 
                AND st.TYPE IN ('SELECT', 'UPDATE', 'DELETE')) 
SELECT st.owner, 
       st.object_name, 
       st.line, 
       st.TYPE, 
       s.text 
  FROM dml_statements st, all_source s 
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE 
                                          FROM full_set fs 
                                    CONNECT BY PRIOR fs.usage_id = 
                                                  fs.usage_context_id 
                                    START WITH fs.usage_id = st.usage_id) 
       AND st.line = s.line 
       AND st.object_name = s.name 
       AND st.owner = s.owner

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 9

Find SQL Statements Containing Function Calls - USER_* Version

WITH my_prog_unit AS (SELECT 'MY_PROCEDURE' object_name FROM DUAL), 
     full_set 
     AS (SELECT ai.usage, 
                ai.usage_id, 
                ai.usage_context_id, 
                ai.TYPE, 
                ai.name 
           FROM user_identifiers ai, my_prog_unit 
          WHERE ai.object_name = my_prog_unit.object_name 
            /* Only with ALL_* AND ai.owner = my_prog_unit.owner */ 
         UNION ALL 
         SELECT st.TYPE, 
                st.usage_id, 
                st.usage_context_id, 
                'type', 
                'name' 
           FROM user_statements st, my_prog_unit 
          WHERE st.object_name = my_prog_unit.object_name 
            /* Only with ALL_* AND st.owner = my_prog_unit.owner */), 
     dml_statements 
     AS (SELECT /* Only with ALL_* st.owner, */ st.object_name, st.line, st.usage_id, st.type 
           FROM user_statements st, my_prog_unit 
          WHERE     st.object_name = my_prog_unit.object_name 
                /* Only with ALL_* AND st.owner = my_prog_unit.owner */ 
                AND st.TYPE IN ('SELECT', 'UPDATE', 'DELETE')) 
SELECT /* Only with ALL_* st.owner, */ 
       st.object_name, 
       st.line, 
       st.TYPE, 
       s.text 
  FROM dml_statements st, all_source s 
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE 
                                          FROM full_set fs 
                                    CONNECT BY PRIOR fs.usage_id = 
                                                  fs.usage_context_id 
                                    START WITH fs.usage_id = st.usage_id) 
       AND st.line = s.line 
       /* Only with ALL_*  AND st.owner = s.owner */ 
       AND st.object_name = s.name


OBJECT_NAME LINE TYPE TEXT
MY_PROCEDURE 6 SELECT    SELECT my_function1 ()
MY_PROCEDURE 18 UPDATE    UPDATE my_data


2 rows selected.

OBJECT_NAME	LINE	TYPE	TEXT
MY_PROCEDURE	6	SELECT	SELECT my_function1 ()
MY_PROCEDURE	18	UPDATE	UPDATE my_data

Statement 10

Using ALL_* views; will not run in LiveSQL. See next statement.

Across All Schemas, All Program Units

WITH full_set 
     AS (SELECT ai.owner, 
                ai.object_name, 
                ai.usage, 
                ai.usage_id, 
                ai.usage_context_id, 
                ai.TYPE, 
                ai.name 
           FROM all_identifiers ai 
         UNION ALL 
         SELECT st.owner, 
                st.object_name, 
                st.TYPE, 
                st.usage_id, 
                st.usage_context_id, 
                'type', 
                'name' 
           FROM all_statements st), 
     dml_statements 
     AS (SELECT st.owner, 
                st.object_name, 
                st.line, 
                st.usage_id, 
                st.TYPE 
           FROM all_statements st 
          WHERE st.TYPE IN ('SELECT', 'UPDATE', 'DELETE')) 
SELECT st.owner, 
       st.object_name, 
       st.line, 
       st.TYPE, 
       s.text 
  FROM dml_statements st, all_source s 
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE 
                                          FROM full_set fs 
                                    CONNECT BY PRIOR fs.usage_id = 
                                                  fs.usage_context_id 
                                    START WITH fs.usage_id = st.usage_id) 
       AND st.line = s.line 
       AND st.object_name = s.name 
       AND st.owner = s.owner

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 11

Using USER_* views

Across All Program Units in Your Schema

WITH full_set 
     AS (SELECT ai.object_name, 
                ai.usage, 
                ai.usage_id, 
                ai.usage_context_id, 
                ai.TYPE, 
                ai.name 
           FROM user_identifiers ai 
         UNION ALL 
         SELECT st.object_name, 
                st.TYPE, 
                st.usage_id, 
                st.usage_context_id, 
                'type', 
                'name' 
           FROM user_statements st), 
     dml_statements 
     AS (SELECT st.object_name, 
                st.line, 
                st.usage_id, 
                st.TYPE 
           FROM user_statements st 
          WHERE st.TYPE IN ('SELECT', 'UPDATE', 'DELETE')) 
SELECT st.object_name, 
       st.line, 
       st.TYPE, 
       s.text 
  FROM user_statements st, user_source s 
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE 
                                          FROM full_set fs 
                                    CONNECT BY PRIOR fs.usage_id = 
                                                  fs.usage_context_id 
                                    START WITH fs.usage_id = st.usage_id) 
       AND st.line = s.line 
       AND st.object_name = s.name


OBJECT_NAME LINE TYPE TEXT
MY_PROCEDURE 6 SELECT    SELECT my_function1 ()
MY_PROCEDURE 18 UPDATE    UPDATE my_data


2 rows selected.

OBJECT_NAME	LINE	TYPE	TEXT
MY_PROCEDURE	6	SELECT	SELECT my_function1 ()
MY_PROCEDURE	18	UPDATE	UPDATE my_data

Additional Information

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