Re: Search for string in all tables in one schema

That will only work if the column name is known..The OP indicated that it could be any of several varchar2 columns in any of the 150 tables..

This makes it much harder and will require PlSql and multiple cursors and at least 2 loops.. - without knowing more about the structure, even pseudo code is not likely to be correct  but , in essence, you will need to build 2 loops ( one for entries in all_tables and, within that, one for all columns in that table ( from all_tab_columns)) then test each column in each table for the string to replace..

Brian Peasland <oracle_dba_at_remove_spam.peasland.com> wrote:

>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
Received on Tue Jul 08 2003 - 08:53:17 CDT