Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Apr 10, 2000

Update Primary/Foreign Key Columns of Numeric/Character Datatype in Oracle

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 1000000
set feed off veri off trims off lines 80

Accept v_column prompt "Enter column name to be updated : " prompt
Accept v_newvalue prompt "Enter the new valid column value without 
quotation marks"
Prompt
Prompt 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 single
Accept v_whereclause prompt "quotation marks : "

prompt

spool TempUpdateColumn.sql

SELECT 'alter table '|| A.table_name || ' disable constraint '||
A.constraint_name || ';'
FROM user_cons_columns A, user_constraints B
WHERE A.constraint_name = B.constraint_name
AND A.table_name = B.table_name
AND 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 B
WHERE A.constraint_name = B.constraint_name
AND A.table_name = B.table_name
AND 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_comments
WHERE column_name = Upper('&v_column');

SELECT 'alter table '|| A.table_name || ' enable constraint '||
A.constraint_name || ';'
FROM user_cons_columns A, user_constraints B
WHERE A.constraint_name = B.constraint_name
AND A.table_name = B.table_name
AND 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 B
WHERE A.constraint_name = B.constraint_name
AND A.table_name = B.table_name
AND A.column_name = Upper('&v_column')
AND B.constraint_type = 'R' ;

SELECT 'SPOOL OFF' FROM DUAL;
set termout on
set pagesize 58
set linesize 80
set feedback on

/* Run query generated above */
spool TempUpdateColumn.log
start TempUpdateColumn.sql

set verify on
set echo on
set heading on

spool off;
Jayanta Sengupta
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date