oracle plsql dynamic loop

Question

I am trying to rewrite the following code, in order to avoid ORA-00942 error (table or view does not exist). It is because during the compilation of my code, the table(MY_TABLE_NAME) still does not exist, therefore I need to make it dynamic.

Here is the code (used generic names)

DECLARE
  C INTEGER := 0;
BEGIN
 SELECT COUNT(1) INTO C FROM USER_TABLES WHERE TABLE_NAME = 'MY_TABLE_NAME';
 IF C > 0 THEN
      DECLARE     
       CURSOR c_var IS SELECT COLUMN_1, COLUMN_2 FROM MY_TABLE_NAME WHERE ACTIVE = 1;
        v_id NUMBER(15);
      BEGIN
        FOR prec IN c_var LOOP
          ......testcode
        END LOOP;
        EXECUTE IMMEDIATE 'testcode';
      END;
 END IF;
END;
/

It is not necessary to use a cursor..so i tried to rewrite it and using a normal for loop because I think it is easier to make the code more dynamic, however I am still struggling

What exactly do you want to do with `MY_TABLE_NAME`? Select one row, several rows, insert, update, delete? — Erich Kitzmueller, Mar 17 '17 at 11:46
Have a look [here](http://stackoverflow.com/questions/41855482/select-from-table-that-does-not-exist/41855631#41855631).Why is this question that different? — Aleksej, Mar 17 '17 at 11:47
@Aleksej well..it is not as easy as at one of my previous posts..i just cannot solve this problem with Execute Immediate or at least I tried but failed because the code here is more complex — NoName123, Mar 17 '17 at 11:52
@ammoq my cursor selects the row and in the begin/end statement I make an update..but why is it relevant? my code fails because of line: CURSOR c_var IS SELECT COLUMN_1, COLUMN_2 FROM MY_TABLE_NAME WHERE ACTIVE = 1;...it cannot compile this line because "MY_TABLE_NAME" does not exist yet — NoName123, Mar 17 '17 at 11:54
@NoName123 obviously, you have to rewrite that part, but it's impossible to say what to write instead when I don't know what you want to archive. — Erich Kitzmueller, Mar 17 '17 at 11:55

score 3 · Accepted Answer · answered Mar 17 '17 at 12:01

Assuming you only want to get the cursor working:

DECLARE
  C INTEGER := 0;
  TYPE MyCurTyp  IS REF CURSOR;
  v_my_cursor    MyCurTyp;
  v_col1      varchar2(20); -- replace varchar2(20) with correct data type
  v_col2      varchar2(20); -- replace varchar2(20) with correct data type
BEGIN
 SELECT COUNT(1) INTO C FROM USER_TABLES WHERE TABLE_NAME = 'MY_TABLE_NAME';
 IF C > 0 THEN
     OPEN v_my_cursor FOR 'SELECT COLUMN_1, COLUMN_2 FROM MY_TABLE_NAME WHERE ACTIVE = 1';
     LOOP
        FETCH v_my_cursor into v_col1, v_col2;
        EXIT WHEN v_my_cursor%notfound;

        -- prepare testcode from v_col1, v_col2

        EXECUTE IMMEDIATE 'testcode';
      END LOOP;
      close v_my_cursor;
 END IF;
END;
/

oracle plsql dynamic loop

1 Answers1