最近遇到如下问题,很郁闷,(在System用户执行的过程)DECLARE
CURSOR cur_query IS
select table_name,column_name from user_tab_columns ;
strsql VARCHAR2(5000);
v_number NUMBER;
BEGIN
FOR rec_query IN cur_query LOOP
strsql := 'SELECT count(*) FROM '|| rec_query.table_name ||' WHERE '|| rec_query.column_name ||' LIKE ''%HOST_var%''' ;
execute immediate strsql INTO v_number;
IF v_number > 0 THEN
dbms_output.put_line(rec_query.table_name||'--'||rec_query.column_name);
END IF;
END LOOP;
END;执行结果
ORA-00942: 表或视图不存在
CURSOR cur_query IS
select table_name,column_name from user_tab_columns ;
strsql VARCHAR2(5000);
v_number NUMBER;
BEGIN
FOR rec_query IN cur_query LOOP
strsql := 'SELECT count(*) FROM '|| rec_query.table_name ||' WHERE '|| rec_query.column_name ||' LIKE ''%HOST_var%''' ;
execute immediate strsql INTO v_number;
IF v_number > 0 THEN
dbms_output.put_line(rec_query.table_name||'--'||rec_query.column_name);
END IF;
END LOOP;
END;执行结果
ORA-00942: 表或视图不存在
在该语句后面加打印语句,看看抛出的是什么表
strsql := 'SELECT count(*) FROM "'|| rec_query.table_name ||'" WHERE '|| rec_query.column_name ||' LIKE ''%HOST_var%''' ;
dbms_output.put_line(strsql ||'--'||v_number);加上这句话,看看走到哪个表时报的错误