CODEWITHSUNDEEP
oracle10gconstraints
glenneroo
glenneroo

Reputation: 2054

Oracle 10g Express: DISABLE CONSTRAINT not really disabling constraints?

I've got a hairy system with almost 650 inter-connected tables (with plenty of bi-directional refs) which is created via Hibernate script. I need to delete left-over (corrupt) data based on an ID which is present in all tables. I've written a script to generate procedures to disable constraints on each of the tables e.g.:

create or replace procedure disable_MyTable as
BEGIN
  FOR r IN (
    SELECT TABLE_NAME, CONSTRAINT_NAME
    FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'MyTable') LOOP
    EXECUTE IMMEDIATE REPLACE(REPLACE(
      'ALTER TABLE #TABLE# DISABLE CONSTRAINT #CON#'
      ,'#TABLE#',r.TABLE_NAME)
      ,'#CON#',r.CONSTRAINT_NAME);
END LOOP;
END;
/

Followed by:

exec disable_MyTable;

Followed by my delete, whereupon I sometimes receive this error:

ORA-02292: integrity constraint (XXX.FK409) violated - child record found

When I check the status of the constraints:

select status from user_constraints where table_name = 'MyTable';

Oracle says all constraints are still ENABLED!

Are procedures being executed in their own instance? Do I need higher privileges?

p.s. I don't care about performance. This is just a clean-up script.

Upvotes: 0

Views: 707

Answers (1)

user330315
user330315

Reputation:

If the statement select status from user_constraints where table_name = 'MyTable' really returns a row, then you need to put double quotes around the #TABLE# placeholder in your ALTER statement. Because in that case MyTable is case sensitive and requires doubles quotes.

You are generating the following statement

ALTER TABLE MyTable DISABLE CONSTRAINT FK409ADEF4HERW;

But it should be:

ALTER TABLE "MyTable" DISABLE CONSTRAINT FK409ADEF4HERW;

Again this assumes that your SELECT statement was using 'MyTable' as shown

Upvotes: 2

Related Questions