Re: How can I disable referential integrity during import?

In article <36187744.3C80_at_hospvd.ch>,   ebaechle_at_hospvd.ch wrote:
> > Try
> > ALTER TABLE 'myTable' DISABLE CONSTRAINT 'myFKConstraint'
> > for each table which caused problems.
>
> Thanks for your answer. This means that there is no switch for
> enabling or disabling all constraints at a time.
>
> Best regards
>
> Emmanuel Baechler
>

Alternatively, you could try the following, it will disable all foreign key constraints on the database, you can amend it to include primary keys etc. To switch the constraints back on, again just amend the cursor definition and the l1_query string substituting disable for enable etc.

Hope this helps

SET SERVEROUT ON
EXEC DBMS_OUTPUT.ENABLE(30000); DECLARE CURSOR get_cons IS
SELECT table_name, constraint_name
FROM all_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED';

BEGIN FOR cons_rec IN get_cons LOOP

  DECLARE

  l1_cursor_handle     INTEGER;
  l1_alter             VARCHAR2(500);
  l1_count             NUMBER;
  l1_dummy             INTEGER;

  l_error              NUMBER;

  BEGIN   l1_cursor_handle := DBMS_SQL.OPEN_CURSOR;

  l1_alter := 'ALTER TABLE '||cons_rec.table_name||' disable constraint '||cons_rec.constraint_name;

  DBMS_OUTPUT.PUT_LINE('Table Name '||cons_rec.table_name||' Constraint Disabled '||cons_rec.constraint_name;

  DBMS_SQL.PARSE(l1_cursor_handle,l1_alter,DBMS_SQL.V8);

  DBMS_SQL.CLOSE_CURSOR(l1_cursor_handle);

  EXCEPTION   WHEN OTHERS THEN
  l_error := SQLCODE;
  DBMS_OUTPUT.PUT_LINE(l_error);
  DBMS_SQL.CLOSE_CURSOR(l1_cursor_handle);

  END; END LOOP;
END;
/

Regards

Gareth

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Oct 05 1998 - 13:00:20 CEST