CREATE TABLE employees AS SELECT * FROM hr.employees

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

Sandra Wrote P1

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

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

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

Identify Duplicated Statements

  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

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