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

From: Matt <mtjohnson_at_gmail.com>
Date: 31 Jul 2006 06:49:45 -0700
Message-ID: <1154353785.808604.189120@s13g2000cwa.googlegroups.com>

Thanks for the reply, but I'm not sure that will work for my use case.

I'm using a FORALL statement, which isn't a real loop, but is supposed to dramatically increase performance for bulk inserts... However, there is no iteration point where I could put v_val := a_items(props.path(i));...

Maybe it's just not possible to do what I'm trying with FORALL :)

Frank van Bortel wrote:
> 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 Mon Jul 31 2006 - 08:49:45 CDT