Home » SQL & PL/SQL » SQL & PL/SQL » Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code")
Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code") [message #563641] Thu, 16 August 2012 04:56 Go to next message
Jana.A
Messages: 8
Registered: August 2012
Location: Coimbatore
Junior Member
Hi ,

I've tried to write the procedure for search the string from whole database. If user give the string as an input the output will be tablename and column in which the string contained in the table,But it's showing an error
Please help me regarding this


CREATE OR REPLACE procedure sample.pr_search_table (p_search varchar )
is
type tb_type is table of varchar(30);
tb_table tb_type:= tb_type();
tb_column tb_type := tb_type();
v_temp varchar(30);

begin

select table_name bulk collect into tb_table from user_tables;
for i in tb_table.first .. tb_table.last loop
--dbms_output.put_line(tb_table(i));
v_temp :=trim(tb_table(i));
pr_search_column(v_temp ,p_search);
end loop;
end;
/

CREATE OR REPLACE procedure sample.pr_search_column (p_tablename varchar,p_search varchar)
is
type tb_type is table of varchar(30);
tb_column tb_type := tb_type();
v_temp varchar(5);
v_temp1 VARCHAR(30);
BEGIN
v_temp1 := p_tablename ;
execute immediate 'select column_name from all_tab_columns where table_name =:v_temp1' bulk collect into tb_column using v_temp1 ;
for i in tb_column.first .. tb_column.last loop
-- dbms_output.put_line( tb_column(i));
execute immediate ' select 1 from :v_temp1 where '|| tb_column(i)|| ' like '|| ''''||'%:p%'||''''|| into v_temp USING in v_temp1, p_search ;

dbms_output.put_line(p_tablename|| ' '|| tb_column(i));
if(v_temp = to_char(1) ) THEN
INSERT INTO tb_insert_search VALUES (p_tablename,tb_column(i));

end if;
v_temp := 0;
end loop;
exception
when NO_DATA_FOUND THEN
dbms_output.put_line(' ');
end;
/


Thanks-Jana
Re: How does DBMS_SQL differ from Native Dynamic Code [message #563643 is a reply to message #563641] Thu, 16 August 2012 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
New question, new topic.
Do NOT mix up the questions otherwise it is impossible to follow and the topic becomes useless for futur readers.
I split this question, next time create a new topic.

Regards
Michel
Re: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code") [message #563644 is a reply to message #563641] Thu, 16 August 2012 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at T. Kyte's find_string function.

Regards
Michel
Re: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code") [message #563648 is a reply to message #563644] Thu, 16 August 2012 05:36 Go to previous messageGo to next message
Jana.A
Messages: 8
Registered: August 2012
Location: Coimbatore
Junior Member
Thanks Michel.
Could you correct my coding from that I can understand my mistake .Please do the needful.

Thanks-Jana
Re: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code") [message #563652 is a reply to message #563648] Thu, 16 August 2012 06:18 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you tell us what error you are getting.
Re: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code") [message #563655 is a reply to message #563648] Thu, 16 August 2012 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No I can't as I can't read not formatted code.
Analyze T. Kyte's procedure and I think you will be able to fix your code yourself.

Regards
Michel
Re: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code") [message #563772 is a reply to message #563655] Thu, 16 August 2012 23:44 Go to previous message
Jana.A
Messages: 8
Registered: August 2012
Location: Coimbatore
Junior Member
Ok thanks Michel.
Previous Topic: Inserts based on column values
Next Topic: Across Schema Stored Procedure Access
Goto Forum:
  


Current Time: Fri Dec 19 19:07:46 CST 2014

Total time taken to generate the page: 0.09800 seconds