Search in all Columns


drop table DB_SEARCH
/

create table DB_SEARCH(
  schema_name varchar2(30),
  table_name  varchar2(30),
  field_name  varchar2(30),
  value       varchar2(2000)
)
/

CREATE OR REPLACE PACKAGE pkg_db_search
IS

  PROCEDURE proc_search (v_phrase VARCHAR2, v_schema VARCHAR2);

END pkg_db_search;
/

CREATE OR REPLACE PACKAGE BODY pkg_db_search
IS

  PROCEDURE proc_search (v_phrase VARCHAR2, v_schema VARCHAR2)
  IS
  /*
  ###############################################################################
  #
  # PACKAGE:               pkg_db_search
  # NAME:                  proc_search
  #
  # AUFGABE:               Durchsuchen des gesamten gegebenen Schemas nach eimen Begriff
  #
  # ENTWICKELT:            Goldbeck, Embshoff
  # LETZTE AENDERUNG:
  #
  ###############################################################################
  */

  SQLTEXT varchar2 (500);

  s_table_name varchar2(30);
  s_field_name varchar2(60);

  TYPE EmpCurTyp IS REF CURSOR;

  c_tablist EmpCurTyp;
  cursor c_tablist2 is SELECT table_name as s_table_name from all_tables;
  r_tablist c_tablist2%rowtype;

  c_fieldlist EmpCurTyp;
  cursor c_fieldlist2 is SELECT column_name, data_type, column_id from all_tab_columns;
  r_fieldlist c_fieldlist2%rowtype;


  BEGIN
    delete from DB_SEARCH;
    --
    -- ... fuer jede Tabelle ...
    --
    sqltext := 'SELECT table_name as s_table_name from all_tables'
    || ' where not table_name like ''DB_SEARCH'' and owner like ''' || v_schema  || '''' 
    || ' order by table_name';
    open c_tablist for sqltext;
    loop
      begin
        fetch c_tablist INTO r_tablist;
        exit when c_tablist%NOTFOUND;
        s_table_name := r_tablist.s_table_name;

        --
        -- ... FUER JEDES FELD ...
        --
        SQLTEXT := 'SELECT column_name, data_type, column_id from all_tab_columns where table_name = ''' || S_TABLE_NAME || ''' and owner like ''' || v_schema  || ''' order by column_name ';
        OPEN C_FIELDLIST FOR SQLTEXT;
        LOOP
          FETCH C_FIELDLIST INTO R_FIELDLIST;
          EXIT WHEN C_FIELDLIST%NOTFOUND;

          SQLTEXT :=
          'INSERT into DB_SEARCH ('||
      '  SELECT '''|| v_schema ||''' as schema_name, '''|| S_TABLE_NAME ||''' as table_name, '''|| R_FIELDLIST.COLUMN_NAME ||''' as field_name, '|| R_FIELDLIST.COLUMN_NAME ||' as value'||
      '  from '|| v_schema || '.' || S_TABLE_NAME ||' where '|| R_FIELDLIST.COLUMN_NAME ||' like '''||V_PHRASE||''''||
      ')';
      execute immediate sqltext;
      commit;

        end loop; -- fuer jedes Feld
      end;
    end loop; -- fuer jede Tabelle


  END proc_search;
END pkg_db_search;
/


begin
  pkg_db_search.proc_search ('Nieder%', 'DBA_RPP');
end;

/

No comments:

Post a Comment