Hi there
Its pretty urgent, got stuck up....
To avoid the undo snapshot error, I am using this procedure to migrate the smaller chunks of huge volume of table data into new tables. This below code works well if the columns are very less. And this procedure is not working if the tables columns are morethan 30 columns and throwing the error PL/SQL: ORA-00913: too many values
CREATE OR REPLACE PROCEDURE migration AS
TYPE array_tp IS TABLE OF tranproc%ROWTYPE;
l_array array_tp;
CURSOR c IS
select * from tranproc p where trunc(date)<=trunc(sysdate)-180;
l_cnt1 NUMBER :=0;
l_cnt2 NUMBER :=0;
l_cnt3 NUMBER :=0;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_array LIMIT 10000;
EXIT WHEN l_array.COUNT = 0;
l_cnt1 := c%ROWCOUNT;
FORALL i IN 1 .. l_array.COUNT
INSERT INTO TMP_Transpoc VALUES l_array(i);
l_cnt2 := l_cnt2 + SQL%rowcount;
END LOOP;
l_cnt3 := c%ROWCOUNT;
CLOSE c;
END;
16 22 PL/SQL: ORA-00913: too many values
its falling
line 16: INSERT INTO TMP_Transpoc VALUES l_array(i);
Above table i.e tranproc has around 80 columns .
i am not pl/sql expert, kindly advise how to resolve it.. i am fine with alternative approach, just i need a smaller chunk commit.