Re: Search for string in all tables in one schema

From: Peter Laursen <pl_at_invalid.dk>
Date: Tue, 8 Jul 2003 16:17:53 +0200
Message-ID: <3f0ad33a$0$22622$ba624c82@nntp04.dk.telia.net>

>
> for all tables in schema a
> do
> if table has varchar2 columns
> search for c:\data and replace it with d:\newdata
> endif
> loop
>

The following is NOT testet but you get the idea:

CREATE OR REPLACE procedure test as
  v_from varchar2(5) := '''d:\''';
  v_to varchar2(5) := '''c:\''';
begin
  for i in (
    select c.table_name, c.column_name
    from user_all_tables t, user_tab_columns c     where t.table_name = c.table_name
    and c.data_type= 'VARCHAR2')
  loop
 execute immediate(

      'update ' || i.table_name || ' set ' || i.column_name ||
      ' = replace(' || i.column_name || ',' || v_from || ',' || v_to

||')'
 );
  end loop;
end test;

Peter Received on Tue Jul 08 2003 - 09:17:53 CDT