Search for string in all tables in one schema

From: Marc Eggenberger <marc.eggenberger_at_itc.alstom.com>
Date: Tue, 8 Jul 2003 14:51:52 +0200
Message-ID: <MPG.1974dcd8fedd80ab9896af@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

-- 
mfg
Marc Eggenberger

Received on Tue Jul 08 2003 - 07:51:52 CDT