How Does Oracle search for fields containing a certain value in all tables of the current user?

How Does Oracle search for fields containing a certain value in all tables of the current user? CreateorreplaceprocedureMY_Pro_SearchKeyWordisv_sqlVARCHAR2 (40

How Does Oracle search for fields containing a certain value in all tables of the current user? Create or replace procedure MY_Pro_SearchKeyWord is v_ SQL VARCHAR2 (40

How Does Oracle search for fields containing a certain value in all tables of the current user?

Create or replace procedure MY_Pro_SearchKeyWord is
V_ SQL VARCHAR2 (4000 );
V_tb_column VARCHAR2 (4000 );
V_cnt NUMBER (18, 0 );
Cursor cur is SELECT 'select' | '"' | t1.table _ name | '". "'| t1.Column _ Name |'" '| ''' |' as col_name, NVL (COUNT (t. "'| t1.Column _ Name |'"), 0) as cnt FROM "'|
T1.table _ name | '"t WHERE t."' | t1.column _ name | '"like'' % keyword % ''' AS str
FROM cols t1 left join user_col_comments t2
On t1.Table _ name = t2.Table _ name and t1.Column _ Name = t2.Column _ Name
Left join user_tab_comments t3
On t1.Table _ name = t3.Table _ name
Where not exists (SELECT t4.Object _ Name FROM User_objects t4
WHERE t4.Object _ Type = 'table'
AND t4.Temporary = 'y'
AND t4.Object _ Name = t1.Table _ Name)
AND (t1.Data _ Type = 'Char 'or t1.Data _ Type = 'varchar2' or t1.Data _ Type = 'varchar ')
Order by t1.Table _ Name, t1.Column _ ID;

BEGIN
FOR I IN cur LOOP
V_ SQL: = I. str; -- Obtain the SQL statement to be executed;
Execute immediate v_ SQL INTO v_tb_COLUMN, v_cnt;
IF v_cnt> 0 THEN
Dbms_output.put_line ('table: '| substr (v_tb_column, 1, instr (v_tb_column ,'. ', 1, 1)-1) |' column: '| substr (v_tb_column, instr (v_tb_column ,'. ', 1, 1) + 1) |
'You' | to_char (v_cnt) | 'records contain the string "keyword "');
End if;
End loop;
EXCEPTION WHEN OTHERS THEN
BEGIN
Dbms_output.put_line (v_ SQL );
Dbms_output.put_line (v_tb_column );
END;
End MY_Pro_SearchKeyWord;

,