Update all char columns to Upper case

[dharma]

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.

[marist89]

1. disable all constraints
2. write a pl/sql loop similar to:

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

[jt]

Why do you need to disable all constraints?

[marist89]

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.

[dharma]

Thanks Jeff
-Dharma