Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get next values of sequence using FORALL. As like FOR LOOP

Ram_AMay 18 2022

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.

Comments
Post Details
Added on May 18 2022
2 comments
1,576 views