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

Raman wrote:
> Hi,
>
> I am using this code to transfer data from 1 table to another. Tables
> used in this script are juts test tables to test the concept.
> Iam not sure whats the problem.
> I am using FOR ALL for teh first time.
>
> The below code is not getting compiled. I get the compilation error in
> second FOR LOOP where FORALL isused.
>
> ----- Code start ----------------
>
> -- Source
>
> CREATE TABLE STATUS
> (status varchar2(20)
> ,description varchar2(100)
> );
>
> CREATE TABLE TST_STATUS
> (status varchar2(20)
> ,description varchar2(100)
> );
>
>
> CREATE OR REPLACE PROCEDURE TEST_ARR IS
>
> -- Source
>
> TYPE s_stat_table IS TABLE OF status%ROWTYPE
> INDEX BY BINARY_INTEGER;
>
> sstat s_stat_table;
>
> -- Target
> TYPE stat_table IS TABLE OF tst_status%ROWTYPE
> INDEX BY BINARY_INTEGER;
>
> tstat stat_table;
>
> BEGIN
>
> SELECT status, description
> BULK COLLECT
> INTO sstat
> FROM status
> ;
>
> FOR i IN s_stat.FIRST.. s_stat.LAST
> LOOP
>
> sstat(i).status := tstat(i).status;
> sstat(i).description := tstat(i).description;
>
> END LOOP;
>
> -- First FOR Loop
> FOR j IN 1..JBT.COUNT
> LOOP
> INSERT INTO TST_STATUS VALUES(sstat(j).status,sstat(j).description);
>
> END LOOP;
>
> COMMIT;
>
> -- Second for loop
> FORALL j IN sstat.FIRST.. sstat.LAST
> INSERT INTO TST_SEG_STATUS
> VALUES (tstat(j));
>
> -- Commit records
> COMMIT;
>
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE( SQLCODE || '-'||SQLERRM);
>
> END TEST_ARR;
> /
> ----- 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 Received on Mon Mar 27 2006 - 16:44:13 CST