Table or view does not exist for a nested implicit cursor

Question

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

1. your code is not aware that you'll be creating table dynamically. 2. Why are you creating table in a loop? 3. Why are you creating a table at all? 4. No need to exit when you loop — Gurwinder Singh, Jan 04 '17 at 17:46
Agree with GurV, but as to why you got that error, [see this question](http://stackoverflow.com/q/33628951/266304). — Alex Poole, Jan 04 '17 at 17:50
Thanks folks. Shouldn't the syntax be on the FOR ii IN ('select * FROM owner1.' || i.VIEW_NAME || ' minus select * FROM owner2.' || i.VIEW_NAME) ? I still get the error regarding the loop. — user3797654, Jan 05 '17 at 19:07

Gurwinder Singh · Accepted Answer · 2017-01-05T18:47:23.133

1

Your code is not aware that you'll be creating a table dynamically (how could it?).

Generally, The PL/SQL Compiler will check your block for the following before executing it:

Check syntax.
Check semantics and security, and resolve names.
Generate (and optimize) bytecode (a.k.a. MCode).

It's on the 2nd Step where your code fails because There isn't a table named new_table prior to compile time.

I think there is no need of Dynamic SQL here. Also, you do not need to put an exit condition when using a for loop on cursor. You can do this:

DECLARE
  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
    FOR ii IN (select * FROM owner1.view1 minus select * FROM owner2.view1)
    LOOP
      DBMS_OUTPUT.put_line('Hey there');
      -- do more stuff
    END LOOP; -- for ii
  END LOOP; -- for i
END;

edited Jan 05 '17 at 18:47

answered Jan 04 '17 at 17:48

Gurwinder Singh

38,557
6
51
76

I used the FOR ii IN (select...) as suggested. I now get an error saying: Error generated at END LOOP; -- for ii PLS-00103: Encountered the symbol "END" when expecting one of the following: – user3797654 Jan 05 '17 at 18:43
@user3797654 did you add anything inside the LOOP and END LOOP?? where i said `-- do stuff`. You have to add your logic there. – Gurwinder Singh Jan 05 '17 at 18:45
Yes I will be adding a few lines of code that will take the select * result set and put that info into a ListAgg and output that data. – user3797654 Jan 06 '17 at 13:05
Please explain properly what you want to do in the question and we may be of help to you – Gurwinder Singh Jan 06 '17 at 13:07
Hi and thank you again! I modified "MY NEW CODE" (above) to show what I am trying to accomplish inside the ii loop. I plan to put the results from the select in the FOR ii loop in a listAgg string. My code currently doesn't make it into the inner loop because of the error. – user3797654 Jan 06 '17 at 14:06
In the code in answer 1, the select in the for ii loop does not work. I need the select for the ii loop in my NEW CODE to work. Any Ideas? – user3797654 Jan 12 '17 at 14:08

Table or view does not exist for a nested implicit cursor

1 Answers1