PL/SQL template for doing a data import

import_template.sql

SET SERVER OUTPUT ON;
WHENEVER SQLERROR EXIT;

DECLARE
  v_sql VARCHAR2(32000);

  CURSOR c_disableConstraints IS

    SELECT 'alter table "'||table_name||'" disable constraint "'||constraint_name||'"' sqlstatement
    FROM USER_CONSTRAINTS
    WHERE constraint_type IN ('R','U','P')
    ORDER BY DECODE (CONSTRAINT_TYPE, 'R', 1, 'U', 2, 'P', 3);

  CURSOR c_enableConstraints IS

    SELECT 'alter table "'||table_name||'" enable constraint "'||constraint_name||'"' sqlstatement
    FROM USER_CONSTRAINTS
    WHERE constraint_type IN ('R','U','P')
    ORDER BY DECODE (CONSTRAINT_TYPE, 'R', 1, 'U', 2, 'P', 3) DESC;

BEGIN

  DBMS_OUTPUT.PUT_LINE('running script on ' || CURRENT_TIMESTAMP);

  FOR v_disableConstraint IN c_disableConstraints LOOP
    v_sql := v_disableConstraint.sqlstatement;
    EXECUTE IMMEDIATE (v_sql);
  END LOOP;

  -- data here...
  
  COMMIT;

  FOR v_enableConstraint IN c_enableConstraints LOOP
    v_sql := v_enableConstraint.sqlstatement;
    EXECUTE IMMEDIATE (v_sql);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('completed on ' || CURRENT_TIMESTAMP);

END
;