Home » Other » General » Character replacement
Character replacement [message #183965] Mon, 24 July 2006 09:26 Go to next message
murugannm
Messages: 2
Registered: November 2005
Junior Member
Hai,

I have a problem, like i want to replace a particular graphic symbol from all the columns of all the tables in a oracle database.

Utl_file and sqlloader wl take to time as the number of tables are too high.

Pl guide me.

Thanks
Re: Character replacement [message #183977 is a reply to message #183965] Mon, 24 July 2006 11:30 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This will replace all C's in all columns of all users' tables with an X. I guess you'll have to replace C and X with CHR(your_graphic_character_number) in order to make it work.
DECLARE
   str   VARCHAR2 (1000);
BEGIN
   FOR cur_r IN (SELECT table_name, column_name
                   FROM user_tab_columns)
   LOOP
      BEGIN
         str :=
               'UPDATE '
            || cur_r.table_name
            || ' SET '
            || cur_r.column_name
            || ' = replace('
            || cur_r.column_name
            || ', ''C'', ''X'')';

         EXECUTE IMMEDIATE (str);
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
END;
/
Re: Character replacement [message #184057 is a reply to message #183977] Tue, 25 July 2006 02:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And maybe add a WHERE INSTR(col,chr) > 0 clause so your archive log tapes don't fly off the spool and decapitate some poor tech. Wink
Re: Character replacement [message #184066 is a reply to message #184057] Tue, 25 July 2006 03:20 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL! Where do you get those ideas?!? Very Happy
Previous Topic: designing a db
Next Topic: Daylight Saving Time
Goto Forum:
  


Current Time: Wed Dec 07 14:48:29 CST 2016

Total time taken to generate the page: 0.09210 seconds