-
Hi,
We have a requirement where we have to update all the columns which have char/varchar datatype to upper case as the data is also required for legacy system.
The one solution i think is to
1.disable all the constraints.
2. write a ddl cursor to update all the char cols using the primary key and execute it.
3. enable constraints.
has anyone come across this? any other ways to do it?
we arent using function based indexes either to solve, as the data is needed and interchanged between oracel and a legacy system.
Thanks
Dharma.
-
1. disable all constraints
2. write a pl/sql loop similar to:
Code:
declare
lsql varchar2(222);
begin
for x in (select table_name, column_name from user_tab_columns
where data_type in ('CHAR','VARCHAR','VARCHAR2')) loop
lsql := 'update ' || x.table_name || ' set ' || x.column_name
|| ' = upper(' || x.column_name || ')';
dbms_output.put_line(lsql);
end loop;
end;
3. re-enable constraints
Jeff Hunter
-
Why do you need to disable all constraints?
J.T.
-
Originally posted by jt
Why do you need to disable all constraints?
If one of the columns you are trying to update is a FK for another table, it will bomb out.
Jeff Hunter
-
Thanks!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|