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!
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
