Re: Search for string in all tables in one schema

From: Chuck <chuckh_at_softhome.net>
Date: 8 Jul 2003 14:01:46 GMT
Message-ID: <Xns93B2660663B1Dchuckhsofthomenet@130.133.1.4>

Sounds like you don't even know which columns might contain the string. Is that correct? If so then you will need to search every varachar2 column of every table in the schema. You can get that info from from the user_tab_columns view. Something like this should work. Beware code is written on the fly and not tested.

declare
  cursor c1 is
    select table_name, column_name
    from user_tab_columns
    where datatype = 'VARCHAR2';
  stmt varchar2(200);
begin
  for r in c1 loop

    stmt := 'update '||r.table_name;
    stmt := stmt ||' set '||r.column_name||' = ''new_value''';
    stmt := stmt || ' where '||r.column_name||' = ''old_value''';

    execute immediate stmt;
  end loop;
end;

Marc Eggenberger <marc.eggenberger_at_itc.alstom.com> wrote in news:MPG.1974dcd8fedd80ab9896af_at_iww.cacti.ch.alstom.com:

> Hi there.
>
> First:
> Oracle 8.1.7 on Windows 2000 Adv Server
>
> I have an application here where some ppl want me to change a string
> value.
>
> In one schema there are a few tables (about 150) and some of them have
> character columns (mostly varchar2) with data in it. Some data is a
> complete path on the filesystem like
>
> c:\data\t1\0001\uatr.trn
> etc etc
>
> This is an old application and nobody knows what the excat layout is
> (its not one I normaly have to care about).
> They now moved the data to anther drive and this stupid application
gets
> all the path information from the database and wants the get those
files
> which does not work with the data on the new drive (stupid app ....)
>
> Is there an easy way to change the information?
>
> Something like
>
> for all tables in schema a
> do
> if table has varchar2 columns
> search for c:\data and replace it with d:\newdata
> endif
> loop
>
> I cant think of an easy way to do this. Maybe there is?
>
> Thanks for any help
Received on Tue Jul 08 2003 - 09:01:46 CDT