Joseph Swamidass, November 08, 2016 - 3:17 am UTC
DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE '%CHAR%' AND TABLE_NAME NOT LIKE 'V\_%' ESCAPE '\' ) LOOP
EXECUTE IMMEDIATE
replace(replace(
q'{SELECT COUNT(*) FROM @@tab where UPPER(@@col) like '%'||:1||'%'}',
'@@tab',t.table_name),
'@@col',t.column_name)
INTO match_count
USING 'REGISTER BOX';
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 7
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
wondering why I'm receiving this error?!
November 09, 2016 - 2:10 am UTC
Yes :-)
So - to assist with your debugging, rather than do this:
execute immediate 'complex string' into match_count using 'REGISTER BOX';
do this:
l_str := 'complex string';
dbms_output.put_line(l_str);
execute immediate l_str into match_count using 'REGISTER BOX';
so you can *see* the SQL we are trying to run.
The answer should become self-evident