Search for a specific text in Oracle database

set serveroutput on size 100000;
declare
–Text to find, case insensitive. Use % for like.
V_TEXT varchar(50) := '%what I want to locate%';
–Tables schema
V_SCHEMA varchar(50) := 'SCHEMA_NAME';
–List of tables to skip; comma-separated or NONE
V_SKIP_NAMES varchar(200) := 'NONE';
–Using temporary table
V_TEMPORARY_TABLE number := 0;
—————————–
V_TOTAL_TABLES number := 0;
V_TOTAL_COLUMNS number := 0;
V_TOTAL_RESULT number := 0;
V_TIMER number;
V_QUERY varchar(4000);
–Select all schema's tables
cursor
V_TABLE_LIST
is
select
OBJECT_NAME
from
ALL_OBJECTS
where
(OBJECT_TYPE = 'TABLE')
and (OWNER = V_SCHEMA)
–Eventual exclusions
and (OBJECT_NAME not in (V_SKIP_NAMES))
–Debug purposes
–and (OBJECT_NAME in ('GM_SFL_SERVICE_FEE_DETAILS'))
order by
OBJECT_NAME
;
begin
V_TIMER := dbms_utility.get_time();
begin
if (V_TEMPORARY_TABLE = 1) then
execute immediate 'create global temporary table TMP_LOOKUP_TEXT (SCHEMA_NAME varchar(50), TABLE_NAME varchar2(50), COLUMN_NAME varchar2(50), COLUMN_VALUE varchar2(1000)) on commit preserve rows';
end if;
exception when others then
dbms_output.put_line('Fail to create temporary table: ' || sqlerrm);
end;
–For each table
for V_TABLE_ITEM in V_TABLE_LIST loop
V_TOTAL_TABLES := V_TOTAL_TABLES + 1;
–dbms_output.put_line('Checking table: ' || V_TABLE_ITEM.OBJECT_NAME);
declare
–Select all table's columns
cursor
V_COLUMN_LIST
is
select
OWNER as SCHEMA_NAME
, TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
from
SYS.ALL_TAB_COLUMNS
where
(OWNER = V_SCHEMA)
and (TABLE_NAME = V_TABLE_ITEM.OBJECT_NAME)
and (COLUMN_NAME not in (V_SKIP_NAMES))
and (DATA_TYPE in ('NVARCHAR2', 'CHAR', 'VARCHAR2'))
order by
COLUMN_NAME
;
begin
–For each column
for V_COLUMN_ITEM in V_COLUMN_LIST loop
V_TOTAL_COLUMNS := V_TOTAL_COLUMNS + 1;
–dbms_output.put_line('Checking column: ' || V_COLUMN_ITEM.COLUMN_NAME);
V_QUERY := 'select ' || V_COLUMN_ITEM.COLUMN_NAME || ' from ' || V_COLUMN_ITEM.SCHEMA_NAME || '.' || V_TABLE_ITEM.OBJECT_NAME || ' where upper(' || V_COLUMN_ITEM.COLUMN_NAME || ') like upper(' || chr(39) || V_TEXT || chr(39) || ')';
–dbms_output.put_line('Query: ' || V_QUERY);
declare
type V_MATCH_RECORD is record
(
V_VALUE varchar2(10000)
);
type V_MATCH_TABLE is table of V_MATCH_RECORD;
type V_MATCH_CURSOR_TYPE is ref cursor;
V_MATCH_LIST V_MATCH_TABLE;
V_MATCH_SELECT V_MATCH_CURSOR_TYPE;
V_INDEX binary_integer;
begin
–Select columns where the value matches
open V_MATCH_SELECT for V_QUERY;
fetch V_MATCH_SELECT bulk collect into V_MATCH_LIST;
close V_MATCH_SELECT;
–Found?
if (V_MATCH_LIST.count > 0) then
V_INDEX := V_MATCH_LIST.first;
while V_INDEX is not null loop
V_TOTAL_RESULT := V_TOTAL_RESULT + 1;
if (V_TEMPORARY_TABLE = 0) then
dbms_output.put_line(V_COLUMN_ITEM.SCHEMA_NAME || '.' || V_COLUMN_ITEM.TABLE_NAME || '.' || V_COLUMN_ITEM.COLUMN_NAME || ' = ' || substr(V_MATCH_LIST(V_INDEX).V_VALUE, 1, 50));
else
execute immediate 'insert into TMP_LOOKUP_TEXT (SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_VALUE) values (V_COLUMN_ITEM.SCHEMA_NAME, V_COLUMN_ITEM.TABLE_NAME, V_COLUMN_ITEM.COLUMN_NAME, V_MATCH_LIST(V_INDEX).V_VALUE)';
end if;
V_INDEX := V_MATCH_LIST.next(V_INDEX);
end loop;
end if;
exception when others then
dbms_output.put_line('Fail to report match: ' || sqlerrm);
end;
end loop;
end;
end loop;
V_TIMER := dbms_utility.get_time() – V_TIMER;
dbms_output.put_line('Read ' || V_TOTAL_COLUMNS || ' columns from ' || V_TOTAL_TABLES || ' tables in ' || (V_TIMER / 1000) || ' seconds. Found ' || V_TOTAL_RESULT || ' match to ' || chr(39) || V_TEXT || chr(39) || ' in schema ' || V_SCHEMA || '.');
begin
if (V_TEMPORARY_TABLE = 1) then
execute immediate 'select * from TMP_LOOKUP_TEXT order by TABLE_NAME, COLUMN_NAME';
end if;
exception when others then
dbms_output.put_line('Fail to list result in temporary table: ' || sqlerrm);
end;
end;
view raw gistfile1.txt hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s