Often the existing data in the database needs to get updated, even if they are part of any primary or foreign keys. In Oracle8.0, it is possible to defer integrity constraints but this feature is unavailable in earlier versions.

This script takes the column name, the new value for the column, and an optional WHERE clause. The WHERE clause should not cause any data conflict when the integrity constraints need to be enabled.

 set termout off;set echo off;set heading off;set pause off;set pagesize 1000;set linesize 120;set feedback off;set trunc off;set termout on;set serveroutput on size 1000000set feed off veri off trims off lines 80Accept v_column prompt "Enter column name to be updated : " promptAccept v_newvalue prompt "Enter the new valid column value without quotation marks"PromptPrompt Now enter a valid where clause, if any, starting with 'WHERE', Prompt else just hit Carriage Return.Prompt If an invalid or conflicting where clause is provided, Prompt constraints may not get enabled.Prompt If the WHERE clause includes any character literal, Prompt then prefix and suffix each of them with two singleAccept v_whereclause prompt "quotation marks : "promptspool TempUpdateColumn.sqlSELECT 'alter table '|| A.table_name || ' disable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type = 'R' ;SELECT 'alter table '|| A.table_name || ' disable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type in ('P','U');SELECT 'update '|| table_name || ' set '|| '&v_column' || '=''' ||'&v_newvalue' ||''''||' '|| '&v_whereclause' || ';'FROM user_col_commentsWHERE column_name = Upper('&v_column');SELECT 'alter table '|| A.table_name || ' enable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type in ('P','U');SELECT 'alter table '|| A.table_name || ' enable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type = 'R' ;SELECT 'SPOOL OFF' FROM DUAL;set termout onset pagesize 58set linesize 80set feedback on/* Run query generated above */spool TempUpdateColumn.logstart TempUpdateColumn.sqlset verify onset echo onset heading onspool off;