FORALL INSERT RETURNS PL/SQL: ORA-00947: not enough values
875308Jan 20 2012 — edited Jan 27 2012Hi ,
Here is my code:
DECLARE
TYPE MY_REC IS RECORD(ENO A1.ENO%TYPE, ENAME A1.ENAME%TYPE);
TYPE MY_VARRAY IS VARRAY(10) OF MY_REC;
MY_AR MY_VARRAY;
TYPE MY_REF IS REF CURSOR return MY_REC;
MY_CUR MY_REF ;
BEGIN
OPEN MY_CUR FOR SELECT * FROM A1;
LOOP
FETCH MY_CUR BULK COLLECT INTO MY_AR limit 100;
FORALL i IN 1..MY_AR.COUNT
INSERT INTO A2 values MY_AR(i);
EXIT WHEN MY_CUR%NOTFOUND;
END LOOP;
CLOSE MY_CUR;
END;
A2 table has 3 columns
A1 table has 2 columns
I want to insert in only 2 columns of A2 from A1 table. If I specify columns from A2 table, still it gives the error.
FORALL i IN 1..MY_AR.COUNT
INSERT INTO A2(deptno,dname) values (MY_AR(i).eno,MY_AR(i).ename);
Please help.
Thanks in advance.