plscope-find-duplcate-sql.sql

/*
PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) 
and SQL statements (as of 12.2) in your PL/SQL code. You can do all sorts of amazing 
deep-dive analysis of your code with PL/Scope, answering questions like:

* Where is a variable assigned a value in a program?
* What variables are declared inside a given program?
* Which programs call another program (that is, you can get down to a subprogram in a package)?
* Find the type of a variable from its declaration.
* Show where specific columns are referenced.
* Locate all SQL statements containing hints.
* Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities.
* Show all locations in your code where you commit or rollback.

And my latest favorite: Locate multiple appearances of same "canonical" SQL statement. 
This script shows you how to do this. 

Doc:  http://docs.oracle.com/database/122/ADFNS/plscope.htm 
Blog: http://stevenfeuersteinonplsql.blogspot.com/2017/01/find-duplicate-sql-statements-with.html
*/

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

-- Sandra Wrote P1
-- She likes p_* for parameters and a very spacious formatting for her SQL - 
-- and all in lower case. So modern!
CREATE OR REPLACE PROCEDURE p1 (p_id NUMBER, p_name OUT VARCHAR2) 
IS 
BEGIN 
   select  
          last_name 
     into  
          p_name 
     from  
          employees 
    where     
          employee_id = p_id; 
END;
/

-- Steven Wrote P2
-- We all know about Steven. He's hooked on *_in, *_out for parameters, 
-- upper-casing all keywords, lower-casing others. Whatever. :-)
CREATE OR REPLACE PROCEDURE p2 (id_in NUMBER, name_out OUT VARCHAR2) 
IS 
BEGIN 
   SELECT last_name 
     INTO name_out 
     FROM EMPLOYEES 
    WHERE employee_id = id_in; 
END;
/

-- Show SQL Statement Information
/*
Brilliant! The signatures are different (no big surprise there; that's a value 
generated by PL/Scope that is guaranteed to be unique across all statements and identifiers). 
But notice that the SQL_IDs are the same - and the SQL statements are the same, too.
*/

  SELECT signature, sql_id, text 
    FROM user_statements 
   WHERE object_name IN ('P1', 'P2') 
ORDER BY line, col;

-- Identify Duplicated Statements
-- This query lists all the SQL statements that appear more than once in my code 
-- (well, all the code that was complied with PL/Scope enabled!).
  SELECT sql_id, text, COUNT (*) 
    FROM user_statements 
   WHERE sql_id IS NOT NULL 
GROUP BY sql_id, text 
  HAVING COUNT (*) > 1;

-- Show Program Units with Duplicate SQL
-- This query shows all the program units that contain duplicate SQL statements, along with the SQL text.
SELECT object_name, 
       line, 
       text 
  FROM user_statements 
 WHERE sql_id IN (  SELECT sql_id 
                      FROM user_statements 
                     WHERE sql_id IS NOT NULL 
                  GROUP BY sql_id 
                    HAVING COUNT (*) > 1) 
 ORDER BY object_name, line;