CODEWITHSUNDEEP
oracle-database
user3797654
user3797654

Reputation: 5

Table or view does not exist for a nested implicit cursor

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

Upvotes: 0

Views: 1306

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

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:

  1. Check syntax.
  2. Check semantics and security, and resolve names.
  3. 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;

Upvotes: 1

Related Questions