PLSQL DML statements inside procedure

Question

I am trying to execute below procedure but it says me to use BULKCOLLECT and FORALL to refactor my code. Is it just a warning or should i not use like below for my procedure. If so, how should i modify the code as i am iterating through an array and i need each value to update into table.

CREATE OR REPLACE PROCEDURE SCHEMA.PR_VALIDATE 
(
FILEARRAY IN STRARRAY,
DUPARRAY OUT STRARRAY,
PASSARRAY OUT STRARRAY,
FAILARRAY OUT STRARRAY,
MISSARRAY OUT STRARRAY
)

IS
dupCount NUMBER;
staCode    VARCHAR2 (10);
fileName VARCHAR2 (50);
dupfileName VARCHAR2(50);
fileId NUMBER;
ID VARCHAR2(20);
BEGIN
for i in 1 .. FILEARRAY.count
   loop
     fileName := FILEARRAY(i);


     SELECT COUNT (T.FILEID), T.FILEID,T.STATUS,T.ID INTO dupCount,fileId,staCode,ID FROM TB_TABLE_NAME T, TB_TABLE_NAME S
     WHERE T.FILEID=S.FILEID
     AND T.STATUS=S.STATUS
     AND T.FILENAME = fileName AND T.STATUS IN ('PASS', 'FAIL)
     GROUP BY T.FILEID,T.STATUS,T.ID;


     IF dupCount>1
     THEN 
     SELECT FILENAME INTO dupfileName FROM TB_TABLE_1
     WHERE STATUS IN ('PASS','FAIL') AND FILEID=fileId
     AND FILENAME != fileName;
     DBMS_OUTPUT.PUT_LINE(dupfileName);

     UPDATE TB_TABLE_1 SET STATUS='DUP' WHERE FILENAME=dupfileName;
     DUPARRAY(DUPARRAY.LAST +1 ) :=dupfileName;
     DBMS_OUTPUT.PUT_LINE(dupfileName);
     END IF;

     IF staCode = 'FAIL'
     THEN
     FAILARRAY(FAILARRAY.LAST+1) :=fileName;
     ELSIF staCode = 'PASS'
     THEN
     PASSARRAY(PASSARRAY.LAST+1) :=fileName;
     END IF;

   end loop;


EXCEPTION
    WHEN NO_DATA_FOUND THEN
    staCode :=NULL;
    MISSARRAY(MISSARRAY.LAST +1 ) :=fileName;
    WHEN OTHERS THEN
        PR_ERRORS('PR_VALIDATE', SQLERRM);
       ROLLBACK;

END;

/

Answer 1

As I mentioned in the comment FORALL here is out of context as business logic is incorporated here but again you can try BULK COLLECT with FOR loop to iterate. But data volume is not that large you can still go with the row-by-row approach. Hope below snippet helps. Also there were some single quotes missing which would have given compile time error.

CREATE OR REPLACE PROCEDURE SCHEMA.PR_VALIDATE(
    FILEARRAY IN STRARRAY,
    DUPARRAY OUT STRARRAY,
    PASSARRAY OUT STRARRAY,
    FAILARRAY OUT STRARRAY,
    MISSARRAY OUT STRARRAY )
IS
  dupCount    NUMBER;
  staCode     VARCHAR2 (10);
  fileName    VARCHAR2 (50);
  dupfileName VARCHAR2(50);
  fileId      NUMBER;
  ID          VARCHAR2(20);
BEGIN
  FOR i IN 1 .. FILEARRAY.count
  LOOP
    fileName := FILEARRAY(i);
    SELECT COUNT (T.FILEID),
      T.FILEID,
      T.STATUS,
      T.ID
    INTO dupCount,
      fileId,
      staCode,
      ID
    FROM TB_TABLE_NAME T,
      TB_TABLE_NAME S
    WHERE T.FILEID =S.FILEID
    AND T.STATUS   =S.STATUS
    AND T.FILENAME = fileName
    AND T.STATUS  IN ('PASS', 'FAIL')
    GROUP BY T.FILEID,
      T.STATUS,
      T.ID;
    IF dupCount>1 THEN
      SELECT FILENAME
      INTO dupfileName
      FROM TB_TABLE_1
      WHERE STATUS IN ('PASS','FAIL')
      AND FILEID    =fileId
      AND FILENAME != fileName;
      DBMS_OUTPUT.PUT_LINE(dupfileName);

      UPDATE TB_TABLE_1 
      SET STATUS='DUP' 
      WHERE FILENAME=dupfileName;

      DUPARRAY(DUPARRAY.LAST +1 ) :=dupfileName;
      DBMS_OUTPUT.PUT_LINE(dupfileName);
    END IF;

    IF staCode                     = 'FAIL' THEN
      FAILARRAY(FAILARRAY.LAST+1) :=fileName;
    ELSIF staCode                  = 'PASS' THEN
      PASSARRAY(PASSARRAY.LAST+1) :=fileName;
    END IF;
  END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  staCode                       :=NULL;
  MISSARRAY(MISSARRAY.LAST +1 ) :=fileName;
WHEN OTHERS THEN
  PR_ERRORS('PR_VALIDATE', SQLERRM);
  ROLLBACK;
END;