In the below procedure sequence value is not incresing with FORALL.
WHere as without FORALL i.e. with FOR LOOP it's increasing.
PLease help me how to get sequnce NEXTVAL like for LOOP using FOR ALL.
This is not generating sequnce next value.
FETCH C_INST bulk collect into V_INST limit 10000;
BEGIN
v_RDCNT := v_RDCNT + V_INST.COUNT;
V_OFC_ID:=SEQ_ID.NEXTVAL;
FORALL REC IN 1..V_INST.COUNT SAVE EXCEPTIONS
INSERT INTO LEG_FRT_MET
If I change like this it's working.It's fetching sequnce NEXTVAL.
BEGIN
FETCH C_INST bulk collect into V_INST limit P_COMMITCOUNTER;
v_RDCNT := v_RDCNT + V_INST.COUNT;
FOR REC IN 1..V_INST.COUNT
LOOP
V_OFC_ID:=SEQ_ID.NEXTVAL;
INSERT INTO LEG_FRT_MET
CREATE OR REPLACE procedure PROC_INST_INSERT( PAR_ID IN VARCHAR2)
as
cursor C_INST is
SELECT C_ID,C_CODE FROM inst_tbl where c_code='ANC';
TYPE T_INST_DATA is table of C_INST%ROWTYPE;
V_INST T_INST_DATA;
TYPE C_CFR_TAB IS REF CURSOR;
C_RFR C_CFR_TAB;
TYPE V_LTT IS TABLE OF LTT%ROWTYPE;
v_LTT_CNT V_LTT := V_LTT();
ERR_CNT NUMBER;
v_dec VARCHAR2(50);
v_RDCNT NUMBER := 0;
V_ERCODE NUMBER;
V_ERMSG VARCHAR2(500);
v_CMT VARCHAR2(500);
V_COUNT NUMBER := 0;
V_OFC_ID NUMBER;
BEGIN
V_OBJECTNAME := 'PROC_INST_INSERT';
v_dec := 'PROC_INST_INSERT - Start';
BEGIN
SELECT COUNT (*) INTO V_COUNT FROM LOG_TBL;
IF V_COUNT > 0
THEN
BEGIN
OPEN C_RFR FOR
SELECT DISTINCT lg.C_ID
FROM log_tbl lg
WHERE STATUS = 'FAILED';
EXCEPTION
WHEN OTHERS
THEN
DBMS\_OUTPUT.PUT\_LINE('Error');
END;
ELSE
OPEN C_RFR FOR
SELECT DISTINCT lg.C_ID
FROM log_tbl lg
where STATUS ='SUCCESS';
end if;
LOOP
COMMIT;
FETCH C_RFR BULK COLLECT INTO v_LTT_CNT LIMIT 10000;
FORALL I IN 1 .. v\_LTT\_CNT.COUNT
insert into LTT
VALUES (v\_LTT\_CNT (I).C\_ID);
COMMIT;
V\_INST := T\_INST\_DATA();
OPEN C_INST;
LOOP
FETCH C_INST bulk collect into V_INST limit 10000;
BEGIN
v_RDCNT := v_RDCNT + V_INST.COUNT;
V_OFC_ID:=SEQ_ID.NEXTVAL;
FORALL REC IN 1..V_INST.COUNT SAVE EXCEPTIONS
INSERT INTO LEG_FRT_MET
(
DRC_CD ,
DRC_TYP ,
C_ID ,
OFC_ID
)
VALUES
(
'CSRC',
'TEMPL',
V_INST(REC).C_ID,
V_OFC_ID
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERR_CNT := SQL%BULK_EXCEPTIONS.COUNT;
FOR I IN 1 .. ERR_CNT
LOOP
V_ERCODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
V_ERMSG := SQLERRM (-V_ERCODE);
v_CMT := 'Error During INSERT2';
ERROR_LOG_PC (V_ERCODE, V_ERMSG, v_CMT);
END LOOP;
END;
BEGIN
v_RDCNT := v_RDCNT + V_INST.COUNT;
V_OFC_ID:=S2BV4CDS_ADMIN.SEQ_ID.NEXTVAL;
FORALL REC IN 1..V_INST.COUNT SAVE EXCEPTIONS
INSERT INTO CDP_INSTRUMENTS(
SEQNO ,
C_ID ,
OFC_ID
)
VALUES
(
SEQ_CD.nextval ,
V_INST(REC).C_ID ,
V_OFC_ID
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERR\_CNT := SQL%BULK\_EXCEPTIONS.COUNT;
FOR I IN 1 .. ERR\_CNT
LOOP
V\_ERCODE := SQL%BULK\_EXCEPTIONS(I).ERROR\_CODE;
V\_ERMSG := SQLERRM (-V\_ERCODE);
v\_CMT := 'Error During INSERT2';
ERROR\_LOG\_PC (V\_ERCODE, V\_ERMSG, v\_CMT);
END LOOP;
END;
EXIT WHEN C\_INST%NOTFOUND;
END LOOP;
CLOSE C_INST;
EXIT WHEN C_RFR%NOTFOUND;
END LOOP;
close C_RFR;;
EXCEPTION
WHEN OTHERS THEN
V_ERCODE := SQLCODE;
V_ERMSG := SQLERRM;
v_CMT := 'Error During PROC_INST_INSERT Procedure';
ERROR_LOG_PC(V_ERCODE,V_ERMSG,v_CMT);
RAISE;
end;
end PROC_INST_INSERT;
Thanks in advance.