DECLARE
   col_cnt   PLS_INTEGER;  -- COLUMN COUNT

   p_cs_id   my_case.cs_id%TYPE := &p_cs_id;  -- CASE PARAMETER

   TYPE  TEMPTABLE IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;  -- TEMPORARY TABLE TYPE

   tTABLES    TEMPTABLE;       -- TEMPORARY TABLE VARIABLE
--   r_temp     tTABLES(i)%ROWTYPE; -- TEMPORARY ROW
   
   TYPE c_type IS REF CURSOR;  -- DYNAMIC CURSOR TYPE
   c_temp  c_type;             -- TEMP DYNAMIC CURSOR
   q_temp  VARCHAR2 (32000);   -- TEMP QUERY STRING

BEGIN

-- GET THE NAMES OF ALL NON-CODE TABLES (THE TABLES THAT DON'T END IN '_TYPE')
SELECT  table_name
BULK COLLECT INTO tTABLES
FROM    all_tables
WHERE   table_name NOT LIKE '%_TYPE'
AND     owner = 'ME';

-- LOOP THRU ALL NON-CODE TABLES
FOR i in tTABLES.first..tTABLES.last LOOP

   -- INITIALIZE COLUMN COUNT TO ZERO
   col_cnt := 0;

   -- MOVE CASE RECORDS

   -- CHECK FOR A COLUMN NAMED 'CS_ID' IN THE CURRENT TABLE (PROD)
   SELECT  COUNT(*)
   INTO    col_cnt
   FROM    ALL_TAB_COLS
   WHERE   TABLE_NAME  = tTABLES(i)
   AND     COLUMN_NAME = 'CS_ID';

   -- IF CS_ID EXISTS IN CURRENT TABLE (PROD)
   IF col_cnt > 0 THEN

      -- SELECT ROWS MATCHING CS_ID FROM *PROD*
      q_temp := 'SELECT * FROM tTABLES(i) WHERE CS_ID = p_cs_id';

      OPEN c_temp FOR q_temp;

      WHILE TRUE LOOP
         FETCH c_temp INTO r_temp;
         EXIT WHEN c_temp%NOTFOUND;
      END LOOP;

      CLOSE c_temp;

      -- DELETE EXISTING ROWS (W/ SAME CS_ID) FROM *TEST*
         -- NOT CODED YET

      -- INSERT ROWS SELECTED INTO *TEST* 
         -- NOT CODED YET

      -- HANDLE ERRORS
         -- NOT CODED YET
         -- ROLLBACK
         -- LOG ERROR
         -- EXIT PROCEDURE

   END IF;

END LOOP;

END;
/

scott@ORA92> SELECT * FROM scott.tab1_type
  2  /

COL1                   COL2
---------------------- ----------------------
ends in _type          not to be inserted

scott@ORA92> SELECT * FROM scott.tab2
  2  /

COL1                   COL2
---------------------- ----------------------
no cs_id column        not to be inserted

scott@ORA92> SELECT * FROM scott.tab3
  2  /

CS_ID       COL1                   COL2
----------- ---------------------- ----------------------
NO MATCH    does not match p_cs_id not to be inserted

scott@ORA92> SELECT * FROM scott.my_case
  2  /

CS_ID       COL1                   COL2                   COL3
----------- ---------------------- ---------------------- ----------------------
MATCH_VALUE matches p_cs_id        insert this row        1st my_case value
MATCH_VALUE matches p_cs_id        insert this row        2nd my_case value

scott@ORA92> SELECT * FROM test.tab1_type
  2  /

no rows selected

scott@ORA92> SELECT * FROM test.tab2
  2  /

no rows selected

scott@ORA92> SELECT * FROM test.tab3
  2  /

no rows selected

scott@ORA92> SELECT * FROM test.my_case
  2  /

CS_ID       COL1                   COL2                   COL3
----------- ---------------------- ---------------------- ----------------------
MATCH_VALUE matches p_cs_id        delete this row        value in test.my_case

scott@ORA92> DECLARE
  2    p_cs_id my_case.cs_id%TYPE := '&g_cs_id';
  3  BEGIN
  4    FOR r_tabs IN
  5  	 (SELECT DISTINCT table_name
  6  	  FROM	 user_tab_columns
  7  	  WHERE  table_name NOT LIKE '%_TYPE'
  8  	  AND	 column_name = 'CS_ID')
  9    LOOP
 10  	 EXECUTE IMMEDIATE
 11  	   'DELETE FROM test.' || r_tabs.table_name
 12  	   || ' WHERE cs_id = :b_cs_id'
 13  	   USING p_cs_id;
 14  	 EXECUTE IMMEDIATE
 15  	   'INSERT INTO test.' || r_tabs.table_name
 16  	   || ' SELECT * FROM ' || r_tabs.table_name
 17  	   || ' WHERE  cs_id = :b_cs_id'
 18  	   USING p_cs_id;
 19    END LOOP;
 20  END;
 21  /
Enter value for g_cs_id: MATCH_VALUE
old   2:   p_cs_id my_case.cs_id%TYPE := '&g_cs_id';
new   2:   p_cs_id my_case.cs_id%TYPE := 'MATCH_VALUE';

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM test.tab1_type
  2  /

no rows selected

scott@ORA92> SELECT * FROM test.tab2
  2  /

no rows selected

scott@ORA92> SELECT * FROM test.tab3
  2  /

no rows selected

scott@ORA92> SELECT * FROM test.my_case
  2  /

CS_ID       COL1                   COL2                   COL3
----------- ---------------------- ---------------------- ----------------------
MATCH_VALUE matches p_cs_id        insert this row        1st my_case value
MATCH_VALUE matches p_cs_id        insert this row        2nd my_case value

scott@ORA92>