Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> FORALL Insert and setting values to specific item in associative array PLS-00431

FORALL Insert and setting values to specific item in associative array PLS-00431

From: Matt <mtjohnson_at_gmail.com>
Date: 28 Jul 2006 11:33:50 -0700
Message-ID: <1154111630.829324.234370@m73g2000cwd.googlegroups.com>


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_table

(a,b,c,d,PID,e,f)
VALUES
('test', props.name(i), props.val(i), NULL,
a_items(props.path(i)), SYSDATE, 'test');

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US