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

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 28 Jul 2006 21:00:37 +0200
Message-ID: <eadml3$qf6$1@news6.zwoll1.ov.home.nl>

Matt schreef:
> 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.
>

  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');

You use a_items(props.path(i)), which is another index (namely: props.path(i)) than i.

Try v_val := a_items(props.path(i));
insert ..., NULL, v_val, SYSDATE...;

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Fri Jul 28 2006 - 14:00:37 CDT