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