CODEWITHSUNDEEP
oracle-databaseplsql
redsoxlost
redsoxlost

Reputation: 1235

Can someone explain the error

declare
  v_tx varchar2(100);
  type myarray is table of EMP%ROWTYPE;
  rec_a myarray;
begin
  v_tx := 'SELECT * FROM EMP';
  execute immediate v_tx
    into rec_a;
  FOR C in 1 .. rec_a.COUNT
  LOOP
    DBMS_output.put_line(rec_a.ename(C));
  END LOOP;
end;

I cant pinpoint the error in it, any help would be very much appreciated.

Upvotes: 0

Views: 40

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30765

Your code has two errors:

  • since you're selecting multiple rows into an array, you need BULK COLLECT
  • you're using the index variable inside the loop wrong: instead of rec_a.ename(C), you have to use rec_a(C).ename

The fixed query is:

  declare
      v_tx varchar2(100);
      type myarray is table of EMP%ROWTYPE;
      rec_a myarray;
  begin
      v_tx := 'SELECT * FROM EMP';
      execute immediate v_tx bulk collect
        into rec_a;
      FOR C in 1 .. rec_a.COUNT
      LOOP
        DBMS_output.put_line(rec_a(C).ename);
      END LOOP;
  end;

Upvotes: 2

Related Questions