| 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  |
 |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
