We help IT Professionals succeed at work.

PL/SQL Procedure returns no rows

fairgame
fairgame asked
on
1,583 Views
Last Modified: 2013-12-07
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.
CODE:
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 '%==%';
begin
open tab_cur;
loop
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;
EXCEPTION
when NO_DATA_FOUND then
Null;
end;
/
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
If you are executing this code from an SQL*Plus prompt, you do not have SET SERVEROUTPUT ON...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.