Update all char columns to Upper case
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Update all char columns to Upper case

  1. #1
    Join Date
    Dec 2000
    Posts
    138

    Question

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    76
    Why do you need to disable all constraints?
    J.T.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Dec 2000
    Posts
    138

    Cool Thanks!

    Thanks Jeff
    -Dharma

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width