Re: Search for string in all tables in one schema

Much better to do this in a single SQL statement than to write a PL/SQL loop to do the same task.

How about an SQL statement similar to the following:

UPDATE my_table
SET my_column=REPLACE(my_column,'c:\data','d:\newdata') WHERE INSTR(my_column,'c:\data')>0;

HTH,
Brian

Marc Eggenberger wrote:
>
> 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
> --
> mfg
> Marc Eggenberger

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"