Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> FORALL Insert and setting values to specific item in associative array PLS-00431
Hello,
I'm having an issue using a FORALL and inserting data into a table, where one of the values in the insert is an item in an Associative Array.
My array looks like this:
TYPE a_itemLookup IS TABLE OF NUMBER INDEX BY VARCHAR2(256); a_items a_itemLookup;
And is built:
FOR j in (SELECT id, path FROM table) LOOP a_items(j.path) := j.id; END LOOP;
Then I do this:
execute immediate 'select name, val, path from ' || p_table BULK COLLECT INTO props;
FORALL i IN 1 .. props.name.COUNT INSERT INTO another_tablea_items(props.path(i)), SYSDATE, 'test');
(a,b,c,d,PID,e,f)
VALUES
('test', props.name(i), props.val(i), NULL,
The reason I'm using the array is I was previously using a sub-select for the PID column above and thought the array might give me a performance increase.
However, when I compile this I get the error:
PLS-00431: bulk SQL attributes must use a single index
I've looked up the error, but I'm still not sure I really understand what it's saying... Is what I'm doing here not possible?
I can compile and run it if I use a_items(props.path(1)) .... (or any number, instead of using i ) ... for the PID value. Of course, that's not valid for what I'm doing.
Anyone have any ideas? Thanks in advance. Received on Fri Jul 28 2006 - 13:33:50 CDT