Re: FORALL Insert error into ORA-00947 - Too many values...

From: <fitzjarrell_at_cox.net>
Date: 28 Mar 2006 06:22:10 -0800
Message-ID: <1143555730.448254.74400@j33g2000cwa.googlegroups.com>

jeddy wrote:
> fitzjarrell_at_cox.net wrote:
> > Raman wrote:
> > > Hi,
> > >
> > > I am using this code to transfer data from 1 table to another....
> > >
> > > ----- Code start ----------------
> > >
> > > -- Second for loop
> > > FORALL j IN sstat.FIRST.. sstat.LAST
> > > INSERT INTO TST_SEG_STATUS
> > > VALUES (tstat(j));
> > >
> > > -- Commit records
> > > COMMIT;
> > >
> > > ----- Code End -------
> > >
> > > Thnaks
> > > -- Raman
> >
> > Let me guess (although I shouldn't need to): you're not using 10gR2,
> > you're using 9iR2. If so, therein lies your 'problem' as such code is
> > not usable in 9i.
> >
> > Upgrade to 10gR2 and I expect your 'problem' will disappear. And
> > always state the Oracle release (all four numbers) and operating system
> > when asking questions, It prevents us from wasting time guessing what
> > release you're using, and, subsequently, what your 'problem' might
> > actually be.
> >
> >
> > David Fitzjarrell

>

> uhhhmmm, I didn't see a line building the table "TST_SEG_STATUS"... So
> I am wondering what the error number was? 'cause I don't think it
> matters that you inserted only one of the values to it.

>

> Also, FORALL shows up, with examples all the way back to 8i, by the
> way. So I'm not sure what is meant by David's message.

>

> I also agree that it is always useful to know what verison you are
> using.

>
> je

The error message is:

SQL> show errors
Errors for PROCEDURE TEST_ARR:

LINE/COL ERROR

---

---

43/1 PL/SQL: SQL Statement ignored
43/13 PL/SQL: ORA-00947: not enough values SQL> And it is a result of trying to use FORALL and collection types in 9i; the code in question:

 	FORALL j IN sstat.FIRST.. sstat.LAST
 		INSERT INTO TST_SEG_STATUS
 			VALUES (tstat(j));

is valid only in 10g.

David Fitzjarrell Received on Tue Mar 28 2006 - 08:22:10 CST