SQL Utilities for Oracle

oracle_utilities.sql

-- PL/SQL Debugging Setup
-- See [PL/SQL Debugging Setup](http://docs.oracle.com/html/B31355_01/plsql_debugging_setup.htm). Essentially, you just need to grant necessary privileges to the user for debugging 
GRANT DEBUG CONNECT SESSION TO USER
GRANT DEBUG ANY PROCEDURE TO USER

-- Find PL/SQL Definition
-- The definition of PACKAGE, PROCEDURE, TRIGGER, FUNCTION can be found in USER_SOURCE table or ALL_SOURCE table. Here is an example.
SELECT name, LISTAGG(text) WITHIN GROUP (ORDER BY line) AS plsql
FROM ALL_SOURCE 
WHERE type='PROCEDURE' AND name='<procedure name in upper case>'
GROUP BY name;

-- Alternatively, you can use the following sql statement.
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','ADD_JOB_HISTORY') FROM DUAL;

-- Find Constraints Information of A Table
-- You can find the constraints information of a table in table *USER_CONSTRAINTS* and *USER_CONS_COLUMNS* as shown below. 
-- Please note constaints could be defined implicitly through a view definition.
SELECT * FROM user_constraints WHERE table_name='EMPLOYEES'
SELECT * FROM user_cons_columns WHERE table_name='EMPLOYEES'

-- Find The Definition of A View
SELECT TEXT FROM user_views WHERE view_name=:view_name

-- Alternatively, you can use the following sql statement.
SELECT DBMS_METADATA.GET_DDL('VIEW','<view name in upper case>') FROM DUAL;