SQL Script Enable Foreign Keys

Updated : 4-Mar-2002
Version : 2.0

Description

Enables all disabled.foreign keys for a given owner. Check out the partner disable foreign keys script.

Parameters

&own - The foreign key owner.

SQL Source

rem whenever oserror exit 1
set serveroutput on size 100000
rem whenever sqlerror exit 2

set verify off
set serveroutput on

ACCEPT own PROMPT 'Enter Value for Constraint Owner : '
PROMPT
declare
   sql_stmt varchar2(1024);

cursor get_cons is
   select owner, table_name,constraint_name
   from all_constraints
   where constraint_type='R' 
   and status = 'DISABLED' and owner = UPPER('&&own');
    
con_rec get_cons%ROWTYPE;

begin

   FOR con_rec IN get_cons LOOP
     BEGIN
       sql_stmt := 'alter table '||con_rec.owner||'.'||con_rec.table_name||
                    ' enable constraint '||con_rec.constraint_name;

      dbms_output.put_line(sql_stmt);

     EXECUTE IMMEDIATE sql_stmt;

     EXCEPTION WHEN others THEN
        dbms_output.put_line('Error Processing '
                           ||con_rec.owner||'.'||con_rec.table_name||' '
                           ||con_rec.constraint_name);
        dbms_output.put_line(SQLERRM);
     END;

   END LOOP;

end;
/

select status,count(*) from all_constraints 
where constraint_type='R' and owner = UPPER('&&own')
group by status;

Previous Oracle Version Links

Version 8.0 Enable Foreign Keys

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal