SQL Script Enable Foreign Keys

Updated : 4-August-1998
Version : 1.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

ACCEPT own PROMPT 'Enter Value for Constraint Owner : '
PROMPT

declare
	w_p_string varchar2(254);
	w_cursor_id integer;
	w_p_exec integer;
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
   w_cursor_id:=dbms_sql.open_cursor;
   FOR con_rec IN get_cons LOOP
     BEGIN
       w_p_string := 'alter table '||con_rec.owner||'.'||con_rec.table_name||
                    ' enable constraint '||con_rec.constraint_name;
       dbms_output.put_line(w_p_string);
       dbms_sql.parse(w_cursor_id,w_p_string,1);
       w_p_exec := dbms_sql.execute(w_cursor_id);
     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);
        dbms_output.put_line('.');
     END;
   END LOOP;
   dbms_sql.close_cursor(w_cursor_id);
end;
/

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

Return to Index of SQL Scripts

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