主要是遍历一个用户下所有包含某个值的所有表名和列名
新手目前没有分,还请体谅
报错误,3,5行
CREATE OR REPLACE procedure SYS.P_FIND(
col_name in varchar
USERNAMES in varchar
) authid current_user
IS
lv_tabname varchar2(50);
lv_colname varchar2(50);
li_count int;
str_sql varchar2(255);
cursor cur_find is
select table_name ,column_name from sys.dba_tab_columns t
where owner='WYHISZS2';
begin
open cur_find;
loop
fetch cur_find into lv_tabname,lv_colname;
exit when cur_find%notfound;
str_sql:='select count(*) into li_count from '||USERNAMES.lv_tabname||' where '||lv_colname||'='||col_name;
execute immediate str_sql;
if (li_count)>0 then
dbms_output.put_line(username.table_name||'--'||username.column_name);
insert into WYHISZS2.FIND values (lv_tabname,lv_colname);
end if;
end loop;
close cur_find;
commit;
--select * from WYHISZS2.FIND;
end;
/