DECLARE
sql_stmt varchar2(400);
cursor c1 is SELECT view_name from all_views where owner = 'owner1' AND view_name like 'IRV_%' OR view_name like 'RD_%' order by view_name;
BEGIN
for i IN c1 loop
sql_stmt := 'create table new_table as select * FROM owner1.view1 minus select * FROM owner2.view1';
dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
for ii IN (SELECT * from new_table) loop
dbms_output.put_line('inner loop');
end loop; -- for ii
execute immediate 'drop table new_table';
exit when c1%NOTFOUND;
end loop; -- for i
END;
I get in the script output:
ORA-06550: line 9, column 32: PL/SQL: ORA-00942: table or view does not exist
Line 9 is: for ii IN (SELECT * from new_table) loop
Thank you in advance.
Ok HERE IS MY NEW CODE: Thank you GurV for your help.
DECLARE
CURSOR c1
IS
SELECT view_name
FROM all_views
WHERE owner = 'DBA_A'
AND view_name LIKE 'IRV_%'
OR view_name LIKE 'RD_%'
ORDER BY view_name;
BEGIN
FOR i IN c1
LOOP
FOR ii IN ('select * FROM DBA_A.' || i.VIEW_NAME || ' minus select * FROM DBA_B.' || i.VIEW_NAME)
LOOP
dbms_output.put_line('inner loop');
-- put the results from the select in the FOR ii loop in a listAgg string
-- do stuff
END LOOP; -- for ii
END LOOP; -- for i
END;
Error generated at END LOOP; -- for ii
PLS-00103: Encountered the symbol "END" when expecting one of the following:
The dbms_output.put_line shows the proper select is generated. Thanks again. Rich