find-sql-with-user-defined-functions.sql

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

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

CREATE TABLE my_data (n NUMBER);

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

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

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;
/

-- Show All Identifiers and Statements - ALL_* Version
-- This query unions together rows from ALL_IDENTIFIERS and ALL_STATEMENTS to provide 
-- a complete picture of your program unit. 
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


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


-- Find SQL Statements Containing Function Calls - ALL_* Version
/*
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.
*/

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


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


-- Across All Schemas, All Program Units
-- Using ALL_* views; will not run in LiveSQL. See next statement.
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


-- Across All Program Units in Your Schema
-- Using USER_* views
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