-- Search for "insert into ... " in PL/SQL code

SELECT * FROM user_source
WHERE REGEXP_LIKE(LOWER(text),' insert into po_lines_all_rpt+\s'); 

-- Search/scan for SSN (000-00-0000) pattern in text fields and scrub 
-- such data 

SELECT  ref_str,
  REGEXP_REPLACE(ref_str,'[0-9]{3}-[0-9]{2}-[0-9]{4}','- SSN Scrubbed -') 
FROM (SELECT '&any_str' ref_str FROM DUAL)
WHERE REGEXP_LIKE(ref_str,'[0-9]{3}-[0-9]{2}-[0-9]{4}');

-- REGEXP_LIKE(ref_str,'[[:digit:]]{3}-[:digit:]{2}-[:digit:]{4}') 
-- also works

-- Get only master table (SALES_TRAN) and exclude backup/reload tables

WITH
 q_tab AS
 (SELECT 'SALES_TRAN_2013'    table_name FROM DUAL UNION
  SELECT 'SALES_TRAN_2014_RL' table_name FROM DUAL UNION
  SELECT 'SALES_TRAN'         table_name FROM DUAL
 )
SELECT table_name
FROM  q_tab
WHERE  REGEXP_INSTR(table_name,'[[:digit:]]')=0;


-- Get numeric transaction codes (finalized transactions)

WITH
 q_tab AS
 (SELECT  '100025'   tran_cd,  'FINAL'      tran_status FROM DUAL UNION
  SELECT  '100026H'  tran_cd,  'HOLD'       tran_status FROM DUAL UNION
  SELECT  '100027I'  tran_cd,  'INCOMPLETE' tran_status FROM DUAL UNION
  SELECT  '100028'   tran_cd,  'FINAL'      tran_status FROM DUAL
 ) 
SELECT  tran_cd, tran_status
FROM  q_tab
WHERE  REGEXP_LIKE(tran_cd, '^[[:digit:]]*$');