SQL remove of constraints

Note to self:

when doing large imports using a sql script in oracle. here's how to remove constrains and then enable them after insert:


This code is useful to disable the constraints in the database.

set serveroutput on;
begin
  for c in (select constraint_name, table_name from user_constraints where constraint_type='R') loop
    execute immediate('alter table '||c.table_name||' disable constraint '||c.constraint_name);
  end loop;
end;
/

the '/' at the end lets sql developer know that this is the end of an inline pl/sql script

then insert the normal sql insert script and when done include this code:

begin
  for c in (select constraint_name, table_name from user_constraints where constraint_type='R') loop
    execute immediate('alter table '||c.table_name||' enable constraint '||c.constraint_name);
  end loop;
end;
/
-- SHOW ENABLED --
select constraint_name, status from user_constraints where constraint_type='R';

When the last line still shows disabled constraints the data is corrupt.

Blobs of type String can be inserted via a workaround:

declare myBLobVar varchar2(32767) := 'paste string here' ;
begin
  update tableWithBlob set blobCol = myBlobVar where id = blah ;
end;