FORALL Insert error into ORA-00947

From: Raman <raman.jathar_at_gmail.com>
Date: 27 Mar 2006 13:57:23 -0800
Message-ID: <1143496643.404160.98650@g10g2000cwb.googlegroups.com>

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
Received on Mon Mar 27 2006 - 15:57:23 CST

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