Spent an embarrassingly long period of time on an error with a FORALL loop. Can you see what my error is?

create table drop_me (x char);

declare
  type record_type is table of char index by pls_integer;
  v_array record_type;
begin
  v_array(1) := 'q';
  v_array(2) := 'r';
  v_array(3) := 's';
  forall v_index in 1 .. v_array.count
    insert into drop_me values v_array(v_index);
  commit;
end;
/

Every time I ran the above block of code, I would get the following error:

Error report -
 ORA-06550: line 9, column 32:
 PL/SQL: ORA-00904: : invalid identifier
 ORA-06550: line 9, column 5:
 PL/SQL: SQL Statement ignored
 00000 -  "line %s, column %s:\n%s"
 *Cause:    Usually a PL/SQL compilation error.
 *Action: 

Answer

I was trying to mix two different syntaxes…one for row type, and one for single-value arrays.

Notice in my insert statement, how there are no parenthesis around the v_array variable:

 insert into drop_me values v_array(v_index);

What it should be, in this instance is this:

insert into drop_me values (v_array(v_index));

Apparently, you can only use the “no parenthesis” version around a rowtype. For example:

create table drop_me (x char);

declare
  type record_type is table of drop_me%rowtype index by pls_integer;
  v_array record_type;
begin
  v_array(1).x := 'q';
  v_array(2).x := 'r';
  v_array(3).x := 's';
  forall v_index in 1 .. v_array.count
    insert into drop_me values v_array(v_index);
  commit;
end;
/

If you’re using a “plain ol’ array” you must use parenthesis around your values clause:

declare
  type record_type is table of char index by pls_integer;
  v_array record_type;
begin
  v_array(1) := 'q';
  v_array(2) := 'r';
  v_array(3) := 's';
  forall v_index in 1 .. v_array.count
    insert into drop_me values (v_array(v_index));
  commit;
end;
/

Hope this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *