Karthik Karthik - 5 months ago 7
SQL Question

Reference to Unintialized collection exception using array PLSQL

I am getting reference to uninitialized collection exception when trying to add element to an array. Please help.Is that correct way to initialize the array as mentioned in my calling code?

CREATE OR REPLACE TYPE SCHEMA.STRARRAY AS TABLE OF VARCHAR2 (255);

CREATE OR REPLACE PROCEDURE SCHEMA.PR_VALIDATE
(
FILEARRAY IN STRARRAY,
DUPARRAY OUT STRARRAY)
IS
dupCount NUMBER;
fileName VARCHAR2 (50);
fileId NUMBER;
dupfileName VARCHAR2(50);
BEGIN
for i in 1 .. FILEARRAY.count
loop
fileName := FILEARRAY(i);


SELECT COUNT (T.FILEID), T.FILEID INTO dupCount,fileId FROM TB_COMPANY T, TB_COMPANY S
WHERE T.FILEID=S.FILEID
AND T.RPDT_ORI_FLE_NM = fileName AND T.RPDT_STA_CD IN ('PASS', 'FAIL')
GROUP BY T.FILEID;


IF dupCount>1
THEN
SELECT RPDT_ORI_FLE_NM INTO dupfileName FROM TB_RDTE_COMPANY_HDR_DT
WHERE RPDT_STA_CD IN ('PR15','PR16') AND RPDT_FLE_ID=fileId
AND RPDT_ORI_FLE_NM != fileName;
DBMS_OUTPUT.PUT_LINE(dupfileName);
DUPARRAY(DUPARRAY.LAST +1 ) :=dupfileName; --Here is the exception.
END IF;
end loop;
EXCEPTION
WHEN OTHERS THEN
PR_RDTE_ERRORS('PR_VALIDATE', SQLERRM);
ROLLBACK;

END;
/


Calling code:

DECLARE
DUPARRAY STRARRAY:=STRARRAY();


BEGIN
PR_VALIDATE (STRARRAY('abc.txt'),DUPARRAY);
END;

MT0 MT0
Answer

DUPARRAY.LAST will get the index of the last element of the array (and not the index of the last non-NULL value of the array) - so if you use DUPARRAY.LAST + 1 you will always exceed the array bounds.

Also, you have not extended the array - which you need to do to add an extra element.

You need to do:

DUPARRAY.EXTEND;
DUPARRAY(DUPARRAY.LAST) :=dupfileName;

You also need to initialise the DUPARRRY inside the procedure (instead of in the calling code):

So something like this:

CREATE OR REPLACE PROCEDURE PR_VALIDATE 
(
  FILEARRAY IN  STRARRAY,
  DUPARRAY  OUT STRARRAY
)
IS
dupCount NUMBER;
fileName VARCHAR2 (50);
dupfileName VARCHAR2(50);
fileId NUMBER;
BEGIN
  DUPARRAY := STRARRAY();

  for i in 1 .. FILEARRAY.count loop
     fileName := FILEARRAY(i);

     SELECT COUNT (T.FILEID), T.FILEID
     INTO   dupCount,fileId
     FROM   TB_COMPANY T
            INNER JOIN TB_COMPANY S
            ON T.FILEID=S.FILEID
     WHERE T.RPDT_ORI_FLE_NM = fileName
     AND   T.RPDT_STA_CD IN ('PASS', 'FAIL')
     GROUP BY T.FILEID;

     IF dupCount>1 THEN 
       SELECT RPDT_ORI_FLE_NM
       INTO   dupfileName
       FROM   TB_RDTE_COMPANY_HDR_DT
       WHERE  RPDT_STA_CD IN ('PR15','PR16')
       AND    RPDT_FLE_ID=fileId
       AND    RPDT_ORI_FLE_NM != fileName;

       DBMS_OUTPUT.PUT_LINE(dupfileName);

      DUPARRAY.EXTEND;
      DUPARRAY(DUPARRAY.LAST) :=dupfileName;
    END IF;
  end loop;
END;
/

Then call it:

DECLARE
  DUPARRAY STRARRAY;
BEGIN
  PR_VALIDATE(STRARRAY('abc.txt'),DUPARRAY);
END;
/