Click here to Skip to main content
15,891,981 members
Articles / Programming Languages / SQL
Tip/Trick

How to drop dependency constrations in Oracle for all tables

Rate me:
Please Sign up or sign in to vote.
3.00/5 (1 vote)
17 Nov 2009CPOL 12.2K   2
BackgroundI had to write a script to do some tables alternation and datamodification. I run into the problems where I had disable existingconstraints and re enable them at the end of the process. I wassearching the web for automated script to do that but whatever I foundwas not exactly w

Background

I had to write a script to do some tables alternation and data modification. I run into the problems where I had disable existing constraints and re enable them at the end of the process. I was searching the web for automated script to do that but whatever I found was not exactly what I need.  

Using the code

This is strait forward script execution. I have commented EXECUTE IMMEDIATE statement because it was giving me the error I still can not resolve, but if I output this to the console and manually run it works like a charm.

Pleases post a comment if you find why it does not EXECUTE IMMEDIATE statement properly.  

SET SERVEROUTPUT ON
DECLARE
  CURSOR cur_UserTables
  IS
    SELECT table_name FROM user_tables;
 
  rec_Tables cur_UserTables%ROWTYPE;
  dynamicSql VARCHAR2(4000);
  tbl_Name   VARCHAR(200);
BEGIN
  OPEN cur_UserTables;
  LOOP
    FETCH cur_UserTables INTO rec_Tables; -- Get the cursor in the record variable.
    EXIT
  WHEN cur_UserTables%NOTFOUND; -- Exit the loop when no more rows are found.
    BEGIN
      DECLARE
        CURSOR cur_depTbl
        IS
          SELECT c.table_name,
            c.constraint_name
          FROM user_constraints p
          JOIN user_constraints c
          ON(p.constraint_name     = c.r_constraint_name)
          WHERE (p.constraint_type = 'P'
          OR p.constraint_type     = 'U')
          AND c.constraint_type    = 'R'
          AND p.table_name         = rec_Tables.table_name;
       
        rec_depTab cur_depTbl%ROWTYPE;
      BEGIN
        OPEN cur_depTbl;
        LOOP
          FETCH cur_depTbl INTO rec_depTab;
          EXIT
        WHEN cur_depTbl%NOTFOUND;
        
          begin
          dynamicSql:= ' alter table ' || rec_depTab.table_name || ' disable constraint ' || rec_depTab.constraint_name || ' cascade;';
          
          dbms_output.put_line(dynamicSql);
          --EXECUTE IMMEDIATE (dynamicSql);
          
          EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: Re run manually: ' || dynamicSql);
          end;
          
          COMMIT;
        END LOOP;
        CLOSE cur_depTbl;
      END;
    END ;
  END LOOP;
  CLOSE cur_UserTables;
END;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Tateeda Media Networks
United States United States

Software development is my passion as well as photography.


If you got a sec stop by to see my photography work at http://sk68.com


Tateeda Media Network

Comments and Discussions

 
SuggestionDont use semicolun after the sql statement Pin
codetayal7-Jun-12 22:55
codetayal7-Jun-12 22:55 
GeneralDo not use a semi-colon when executing sql statements Pin
jakier15-Dec-09 21:12
jakier15-Dec-09 21:12 
When exceuting a PL/SQL block, a semi-colon should be add at the end. But for sql statements, semi-colon is not accepted.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.