Re: Search for string in all tables in one schema

#1

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 (DOT) peasland.com> wrote:

Quote:

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

#2

"Marc Eggenberger" <marc.eggenberger (AT) itc (DOT) alstom.com> wrote

Quote:

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?

You are welcome to try OraGrep from
www.niall.litchfield.dial.pipex.com/misc.htm It should generate a logfile to
provide the basis for the updates that Brian provides.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

#3

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 (DOT) alstom.com> wrote in
news:MPG.1974dcd8fedd80ab9896af (AT) iww (DOT) cacti.ch.alstom.com:

Quote:

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

#4

Hi Marc Eggenberger, thanks for writing this:

Quote:

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

Though it's not exactly what you're looking for, you might find some
useful info in the following document (url will wrap):

http://asktom.oracle.com/pls/ask/f?p...::NO::F4950_P8
_DISPLAYID,F4950_P8_CRITERIA:350017644883,

It's about how Tom set up the search "engine" on his AskTom site.
--
[:%s/Karsten Farrell/Oracle DBA/g]

#5

"Ryan Gaffuri" <rgaffuri (AT) cox (DOT) net> wrote

Quote:

"Niall Litchfield" <n-litchfield (AT) audit-commission (DOT) gov.uk> wrote

You are welcome to try OraGrep from
www.niall.litchfield.dial.pipex.com/misc.htm It should generate a
logfile to
provide the basis for the updates that Brian provides.

i noticed you mentioned an oracle myhs presentation. anyway you could
post that? Its always good to make sure Im not spouting off the wrong
thing

.

Hmm, well I have updated www.niall.litchfield.dial.pipex.com/index.htm (but
I only get the cached page unless I specify the full URL) so it should be
there now. If not can you let me know by email and I'll try to work out HTML
101

The key message though is not to trust 'names' but to verify by
testing. That applies to presentations about Myths as well as the myths
themselves.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Search for string in all tables in one schema

comp.databases.oracle.server comp.databases.oracle.server

Re: Search for string in all tables in one schema - 07-08-2003 , 08:53 AM

Re: Search for string in all tables in one schema - 07-08-2003 , 08:59 AM

Re: Search for string in all tables in one schema - 07-08-2003 , 09:01 AM

Re: Search for string in all tables in one schema - 07-08-2003 , 11:35 AM

Re: Search for string in all tables in one schema - 07-09-2003 , 08:14 AM