==========================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');
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment