rem -----------------------------------------------------------------------
rem Filename:   Create_Copy.sql
rem Purpose:    Copy table from one database to another
rem Notes:      Creates a sql script file to easily copy a table from one
                database to another. The created script file disables all the
                constraints to and from the destination table. It also
                disables any triggers on the destination table. It then
                truncates the destination table and executes a copy command
                to copy the contents from the source database to the
                destination database. It then enables all the constraints
                and triggers associated with the table.
rem Warning:    Use with extreme caution!!! Remember to run Tab_copy.sql.
rem Date:       02-Sep-2000
rem Author:     Trevor Vermaak <trevorv@medscheme.co.za>
rem -----------------------------------------------------------------------

-- STEPS:
--
-- Disable constraints
-- Disable triggers
-- Copy
-- Enable constraints
-- Enable triggers

SET SERVEROUTPUT ON
PROMPT Enter the table's name you want to copy
DEFINE tname = &table_name
PROMPT
PROMPT Enter the FROM database id
DEFINE dbname = &database_id
PROMPT
PROMPT Enter the FROM user id
DEFINE uname = &user_id

SPOOL  TAB_COPY.SQL
DECLARE
   v_table_name     VARCHAR2( 30) := '&&tname';
   v_user_name      VARCHAR2( 30) := '&&uname';
   v_db_name        VARCHAR2( 30) := '&&dbname';

BEGIN
   dbms_output.enable( 100000);
   v_table_name := UPPER( v_table_name);
   v_user_name  := UPPER( v_user_name);
   v_db_name    := UPPER( v_db_name);

-- Disable the constraints from other tables
   FOR c1 IN (SELECT con.constraint_name,
                     con.status
              FROM   user_constraints con
              WHERE  con.table_name = v_table_name
              AND    con.constraint_type = 'R'
             )
   LOOP
      IF c1.status = 'ENABLED' THEN
         dbms_output.put_line
         ( 'PROMPT Disable constraint '|| c1.constraint_name||
           ' on table '|| v_table_name
         );
         dbms_output.put_line
         ( 'ALTER TABLE '|| v_table_name|| ' DISABLE CONSTRAINT '||
            c1.constraint_name
         );
         dbms_output.put_line( '/');
      ELSE
         dbms_output.put_line
         ( 'PROMPT Constraint '|| c1.constraint_name||
           ' on table '|| v_table_name||
           ' is already DISABLED.'
         );
         dbms_output.put_line
         ( 'REM ALTER TABLE '|| v_table_name|| ' DISABLE CONSTRAINT '||
            c1.constraint_name
         );

      END IF;
   END LOOP;

-- Disable the constraints to the table on other tables
   FOR c1 IN (SELECT con.table_name,
                     con.constraint_name,
                     con.status
              FROM   user_constraints con
              WHERE  con.r_constraint_name IN
                     (SELECT con1.constraint_name
                      FROM   user_constraints con1
                      WHERE  con1.table_name = v_table_name
                      AND    con1.constraint_type IN ('P', 'U'))
              AND    con.constraint_type = 'R'
             )
   LOOP
      IF c1.status = 'ENABLED' THEN
         dbms_output.put_line
         ( 'PROMPT Disable constraint '|| c1.constraint_name||
           ' on table '|| c1.table_name
         );
         dbms_output.put_line
         ( 'ALTER TABLE '|| c1.table_name|| ' DISABLE CONSTRAINT '||
            c1.constraint_name
         );
         dbms_output.put_line( '/');
      ELSE
         dbms_output.put_line
         ( 'PROMPT Constraint '|| c1.constraint_name||
           ' on table '|| c1.table_name||
           ' is already DISABLED.'
         );
         dbms_output.put_line
         ( 'REM ALTER TABLE '|| c1.table_name|| ' DISABLE CONSTRAINT '||
            c1.constraint_name
         );

      END IF;
   END LOOP;

-- Disable the triggers on the table
   FOR c1 IN (SELECT trg.trigger_name,
                     trg.status
              FROM   user_triggers trg
              WHERE  trg.table_name = v_table_name
             )
   LOOP
      IF c1.status = 'ENABLED' THEN
         dbms_output.put_line
         ( 'PROMPT Disable trigger '|| c1.trigger_name||
           ' on table '|| v_table_name
         );
         dbms_output.put_line
         ( 'ALTER TRIGGER '|| c1.trigger_name|| ' DISABLE'
         );
         dbms_output.put_line( '/');
      ELSE
         dbms_output.put_line( 'PROMPT Trigger '|| c1.trigger_name||
                               ' on table '|| v_table_name||
                               ' is already DISABLED.');
      END IF;
   END LOOP;

   dbms_output.put_line( 'PROMPT Truncate table '|| v_table_name);
   dbms_output.put_line( 'TRUNCATE TABLE '|| v_table_name);
   dbms_output.put_line( '/');

--   dbms_output.put_line( 'PROMPT Delete the table');
--   dbms_output.put_line( 'DELETE '|| v_table_name);
--   dbms_output.put_line( '/');

   dbms_output.put_line( 'PROMPT Copy the table from '|| v_db_name);
   dbms_output.put_line( 'COPY FROM '|| v_user_name|| '@'|| v_db_name|| ' -');
   dbms_output.put_line( 'INSERT '|| v_table_name ||' USING -');
   dbms_output.put_line( 'SELECT * FROM '|| v_table_name);

--   dbms_output.put_line( 'SELECT '|| v_table_name);
--   FOR c1 IN (SELECT utcol.column_name
--              FROM   user_tab_columns utcol
--              WHERE  utcoltable_name = v_table_name)
--   LOOP
--      dbms_output.put_line( utcol.column_name|| ', -');
--   END LOOP
--   dbms_output.put_line( 'FROM '|| v_table_name);

--
-- Enable the constraints from other tables
   FOR c1 IN (SELECT con.constraint_name,
                     con.status,
                     con2.table_name
              FROM   all_constraints con,
                     all_constraints con2
              WHERE  con.table_name = v_table_name
              AND    con.constraint_type = 'R'
              AND    con.r_constraint_name = con2.constraint_name
             )
   LOOP
      dbms_output.put_line
      ( 'PROMPT Enable constraint '|| c1.constraint_name||
        ' to table '|| c1.table_name
      );
      dbms_output.put_line
      ( 'PROMPT on table '|| v_table_name
      );

      dbms_output.put_line
      ( 'ALTER TABLE '|| v_table_name|| ' ENABLE CONSTRAINT '||
         c1.constraint_name
      );
      dbms_output.put_line( '/');
   END LOOP;

-- Enable the constraints to the table on other tables
   FOR c1 IN (SELECT con.table_name,
                     con.constraint_name,
                     con.status
              FROM   user_constraints con
              WHERE  con.r_constraint_name IN
                     (SELECT con1.constraint_name
                      FROM   user_constraints con1
                      WHERE  con1.table_name = v_table_name
                      AND    con1.constraint_type IN ('P', 'U'))
              AND    con.constraint_type = 'R'
             )
   LOOP
      dbms_output.put_line
      ( 'PROMPT Enable constraint '|| c1.constraint_name||
        ' on table '|| c1.table_name
      );

      dbms_output.put_line
      ( 'ALTER TABLE '|| c1.table_name|| ' ENABLE CONSTRAINT '||
         c1.constraint_name
      );
      dbms_output.put_line( '/');
   END LOOP;

-- Enable the triggers on the table
   FOR c1 IN (SELECT trg.trigger_name,
                     trg.status
              FROM   user_triggers trg
              WHERE  trg.table_name = v_table_name
             )
   LOOP
      dbms_output.put_line
      ( 'PROMPT Enable trigger '|| c1.trigger_name||
        ' on table '|| v_table_name
      );
      dbms_output.put_line
      ( 'ALTER TRIGGER '|| c1.trigger_name|| ' ENABLE'
      );
      dbms_output.put_line( '/');
   END LOOP;

END;
/
SPOOL OFF