troubleshooting Question

PL/SQL Procedure returns no rows

Avatar of fairgame
fairgameFlag for India asked on
Oracle Database
2 Comments1 Solution1588 ViewsLast Modified:
I'm trying to write a PL/SQL Procedure which searches all the tables in user schema for a particular string, Please find my code below, it doesn't return any out put, please advice.
CREATE OR REPLACE procedure search_tables as
v_table_name user_tab_columns.TABLE_NAME%TYPE;
v_column_name user_tab_columns.COLUMN_NAME%type;
v_tab user_tab_columns.table_name%type;
v_search_string varchar2(200):='%scott%';
cursor tab_cur is select table_name,column_name from user_tab_columns where data_type='VARCHAR2' and table_name not like '%==%';
open tab_cur;
fetch tab_cur into v_table_name,v_column_name;
exit when tab_cur%notfound;
execute immediate 'select distinct '||''''||v_table_name||''''||' from '||v_table_name||' where '||v_column_name||' like upper('||''''||v_search_string||''''||')' into v_tab;
dbms_output.put_line('The string appears in '||v_tab);
end loop;
close tab_cur;
when NO_DATA_FOUND then
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros