Re: Searching a value into the DB

From: Acuna Munoz Cristian Alfredo <cracuna_at_anakena.dcc.uchile.cl>
Date: 2 Mar 2001 19:12:23 GMT
Message-ID: <97oran$atd$1_at_sunsite.dcc.uchile.cl>

Support <support_at_please_do_not_spam_bilbao.com> wrote:
>I know it is a simple script, but ...
 

>How could I find a value among every tables?
 

>The value is 'XXXXXX', but I do not know the name of the field nor the
>name of the table...
 

>Any easy script?
 

>==============
>MIRELLO
I wrote the next pl/sql (it uses dynamic sql):

set serveroutput on size 1000000;
set termout off;
set verify off;
set pages 0;
set feedback off;
spool searching.wri;
declare
  cursor C_tables is
    select owner

          ,table_name
    from all_tables;

  cursor C_columns(Powner      char
                  ,Ptable_name char) is

    select column_name
    from all_tab_columns
    where owner = Powner
    and table_name = Ptable_name;

  Vowner          varchar2(50);
  Vtable_name     varchar2(50);
  Vcolumn_name    varchar2(50);
  aux             integer;
  Vquery          varchar2(500);
  Vcount          number;
  rows            integer;

begin
  dbms_output.put_line('Searching ...');   open C_tables;
  loop
    fetch C_tables into Vowner

                       ,Vtable_name;

    exit when C_tables%notfound;
    open C_columns(Vowner

                  ,Vtable_name);
    loop
      fetch C_columns into Vcolumn_name;
      exit when C_columns%notfound;
      aux    := dbms_sql.open_cursor;


      Vquery := 'select count(*) from ' || Vowner || '.' || Vtable_name ||
                ' where ' || Vcolumn_name || ' = 1';

/*
                                                 ^-- change 1 for your value

*/

      begin
        dbms_sql.parse(aux,Vquery,0);
        dbms_sql.define_column(aux,1,Vcount);
        rows := dbms_sql.execute(aux);
        loop
          if dbms_sql.fetch_rows(aux) > 0 then
            dbms_sql.column_value(aux, 1, Vcount);
            if Vcount > 0 then
              dbms_output.put_line('Owner: ' || Vowner || ' ' ||
                                   ' - Table: ' || Vtable_name);
            end if;
          else
            exit;
          end if;
        end loop;
      exception
        when others then
          null;
      end;
      dbms_sql.close_cursor(aux);

    end loop;
    close C_columns;
  end loop;
  close C_tables;
end;
/ Received on Fri Mar 02 2001 - 20:12:23 CET