Search a string/number/date value in your schema in oracle

Search a string/number/date value in your schema
-----------------------------------------------
------------------------------------------------

1. Search Proceduree
---------------------

CREATE OR REPLACE PROCEDURE search_db_test (
p_search VARCHAR,
p_type VARCHAR
)
IS
TYPE tab_name_arr IS VARRAY (10000) OF VARCHAR2 (256);

v_tab_arr1 tab_name_arr;
v_col_arr1 tab_name_arr;
v_amount_of_tables NUMBER (10);
v_amount_of_cols NUMBER (10);
v_search_result NUMBER (10);
v_result_string VARCHAR2 (254);
BEGIN
v_tab_arr1 := tab_name_arr ();
v_col_arr1 := tab_name_arr ();
v_col_arr1.EXTEND (1000);

SELECT COUNT (table_name)
INTO v_amount_of_tables
FROM user_tables;

v_tab_arr1.EXTEND (v_amount_of_tables);

FOR i IN 1 .. v_amount_of_tables
LOOP
SELECT table_name
INTO v_tab_arr1 (i)
FROM (SELECT ROWNUM a, table_name
FROM user_tables
ORDER BY table_name)
WHERE a = i;
END LOOP;

FOR i IN 1 .. v_amount_of_tables
LOOP
SELECT COUNT (*)
INTO v_amount_of_cols
FROM user_tab_columns
WHERE table_name = v_tab_arr1 (i) AND data_type = p_type;

IF v_amount_of_cols <> 0
THEN
FOR j IN 1 .. v_amount_of_cols
LOOP
SELECT column_name
INTO v_col_arr1 (j)
FROM (SELECT ROWNUM a, column_name
FROM user_tab_columns
WHERE table_name = v_tab_arr1 (i) AND data_type = p_type)
WHERE a = j;

IF p_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
THEN

EXECUTE IMMEDIATE 'select count(*) from '
|| v_tab_arr1 (i)
|| ' where lower('
|| v_col_arr1 (j)
|| ') like '
|| ''''
|| '%'
|| LOWER (p_search)
|| '%'
|| ''''
INTO v_search_result;
END IF;

IF p_type IN ('DATE')
THEN
EXECUTE IMMEDIATE 'select count(*) from '
|| v_tab_arr1 (i)
|| ' where '
|| v_col_arr1 (j)
|| ' = '
|| ''''
|| p_search
|| ''''
INTO v_search_result;
END IF;

IF p_type IN ('NUMBER', 'FLOAT')
THEN
EXECUTE IMMEDIATE 'select count(*) from '
|| v_tab_arr1 (i)
|| ' where '
|| v_col_arr1 (j)
|| ' = '
|| p_search
INTO v_search_result;
END IF;

IF v_search_result > 0
THEN
v_result_string := v_tab_arr1 (i) || '.' || v_col_arr1 (j);

EXECUTE IMMEDIATE 'insert into search_db_results values ('
|| ''''
|| v_result_string
|| ''''
|| ')';

COMMIT;
END IF;
END LOOP;
END IF;
END LOOP;
END;
/

2. need to create this table first
===================================

CREATE TABLE SEARCH_DB_RESULTS ( RESULT VARCHAR2(1024))

3. execute statements
=========================

exec search_db_test(999,'NUMBER')

exec search_db_test('iqbal','VARCHAR2')

exec search_db_test('halim','VARCHAR2')--string in lower case

exec search_db_test('12-Jan-10','DATE')

exec search_db_test(1000,'NUMBER')


4. find the output
========================

select * from SEARCH_DB_RESULTS


==========================More fast one is ============================
Author:- Tom kyte

CREATE OR REPLACE PROCEDURE basel2.find_string (p_str IN VARCHAR2)
AUTHID CURRENT_USER
AS
l_query LONG;
l_case LONG;
l_runquery BOOLEAN;
l_tname VARCHAR2 (2000);
l_cname VARCHAR2 (4000);

TYPE rc IS REF CURSOR;

l_cursor rc;
BEGIN
DBMS_APPLICATION_INFO.set_client_info ('%' || UPPER (p_str) || '%');

FOR x IN (SELECT *
FROM user_tables)
LOOP
l_query :=
'select distinct '''
|| x.table_name
|| ''', $$
from '
|| x.table_name
|| '
where ( 1=0 ';
l_runquery := FALSE;
l_case := NULL;

FOR y IN (SELECT *
FROM user_tab_columns
WHERE table_name = x.table_name
AND data_type IN ('VARCHAR2', 'CHAR')) ----you add here more datatype
LOOP
l_runquery := TRUE;
l_query :=
l_query
|| ' or upper('
|| y.column_name
|| ') like userenv(''client_info'') ';
l_case :=
l_case
|| '||'' ''|| case when upper('
|| y.column_name
|| ') like userenv(''client_info'') then '''
|| y.column_name
|| ''' else NULL end';
END LOOP;

IF (l_runquery)
THEN
l_query := REPLACE (l_query, '$$', SUBSTR (l_case, 8)) || ')';

BEGIN
OPEN l_cursor FOR l_query;

LOOP
FETCH l_cursor
INTO l_tname, l_cname;

EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ('Found in ' || l_tname || '.' || l_cname);
END LOOP;

CLOSE l_cursor;
END;
END IF;
END LOOP;
END;
/

--grant execute on find_string to public;


exec basel2.find_string ('halim');