Search in oracle DB

Материал из Wiki
Перейти к: навигация, поиск
declare
search_word varchar2(20):= '<YOUR WORD>';

co Pls_Integer;
cursor c2 (se varchar2) is
select owner, name, line 
       from dba_source 
       where text like se;

cursor c1 is
select col.owner, col.table_name, col.column_name
  from dba_tab_columns col, 
       dba_objects obj
  where
       obj.owner=col.owner and 
       col.table_name = obj.object_name and
       col.data_type in ('VARCHAR2', 'CHAR%') and
       obj.object_type='TABLE' and
       obj.owner like '%_DB';
begin
    for c1_rec in c1 loop
        execute immediate 'select count(*) from ' || c1_rec.owner || '.' || c1_rec.table_name || ' where ' || c1_rec.column_name || ' like ''%' || search_word || '%'''  into co;
        if (co > 0) then
            dbms_output.put_line('Table: ' || c1_rec.owner || '.' || c1_rec.table_name || ' Field: ' || c1_rec.column_name || ' Found: ' || co);
        end if;
    end loop;
    dbms_output.put_line('Search in procedures:');
    for c2_rec in c2('%' || search_word || '%') loop
        dbms_output.put_line('Object: ' || c2_rec.owner || '.' || c2_rec.name || ' Line: ' || c2_rec.line);
    end loop;
end;
/