oracle add select statement in oracle bulk forall collect loop

Question

I have the following plsql statement:

DECLARE
VPERIODID          VARCHAR2 (10);
vPROCSESSID        NUMBER;
VAUDITORID         NUMBER;

CURSOR c
IS
     SELECT *
      FROM view_cost_noabc
     WHERE OAUDITORID = 477 AND FL <> 'Ο' AND yearid < 2015
  ORDER BY ID;

TYPE nt_type IS TABLE OF c%ROWTYPE;

l_arr              nt_type;   
TYPE vPREVPERIODVALUE IS TABLE OF NUMBER;

vprev              vPREVPERIODVALUE;

dml_errors         EXCEPTION;

BEGIN
  SELECT fn_periodcurrent INTO vPERIODID FROM DUAL;
  SELECT fn_processcurrent INTO vPROCSESSID FROM DUAL;

OPEN c;

LOOP
   FETCH c
   BULK COLLECT INTO l_arr
   LIMIT 500;

   EXIT WHEN l_arr.COUNT = 0;

   BEGIN
      FOR r IN 1 .. l_arr.COUNT
      LOOP
        vprev (r).vPREVPERIODVALUE :=
           'SELECT NVL (SUM (CURRPERIODVALUE), 0)
              FROM LIQUIDATIONSDETAILS
             WHERE     APPOINTOFCAID = l_arr (r).ID
                   AND PROCESSID < vPROCSESSID
                   AND SUBSTR (PROCESSID, 5, 2) = 12
                   AND AUDITORID = 477';
      END LOOP;

     FORALL i IN 1 .. l_arr.COUNT
        INSERT INTO liquidationsdetails (....)
             VALUES (...);
  EXCEPTION
     WHEN DML_ERRORS
     THEN
        NULL;
  END;
  EXIT WHEN c%NOTFOUND;
END LOOP;   
CLOSE C;
COMMIT;
END;

I want to set the results using a select statement in oracle bulk forall collect loop to a array variable. In the above code I receive [Error] Execution (60: 42): PLS-00487: Invalid reference to variable 'NUMBER' PL/SQL: Statement ignored.

I prefer to use the SELECT SUM () statement in forall loop if is it possible.

Any idea?

Answer 1

In the code in the question you're trying to assign a string to an element in a table of numbers; in the comments you've modified that to select a numeric value, But in both you've got the table element reference wrong; this:

INTO vprev (r).vPREVPERIODVALUE

should just be:

INTO vprev (r)

The vPREVPERIODVALUE is the type of vprev (and is TABLE OF NUMBER), so you don't refer to that type directly.

You also don't seem to be initialising or extending that collection before trying to assign values to its elements.

Doing a single select for each iteration of the cursor loop seems rather odd and inefficient anyway; you could do an outer join in the cursor instead, something like:

CURSOR c
IS
    SELECT vcn.ID, vcn.OAUDITORID,
             NVL (SUM (CURRPERIODVALUE), 0) AS PREVPERIODVALUE
      FROM view_cost_noabc vcn
 LEFT JOIN LIQUIDATIONSDETAILS ld
        ON ld.APPOINTOFCAID = vcn.ID
       AND ld.AUDITORID = vcn.OAUDITORID
       AND ld.PROCESSID < vPROCSESSID
       AND SUBSTR (ld.PROCESSID, 5, 2) = 12
     WHERE vcn.OAUDITORID = 477 AND vcn.FL <> 'Ο' AND vcn.yearid < 2015
  GROUP BY vcn.ID, vcn.OAUDITORID
  ORDER BY vcn.ID;

Then you don't need vPREVPERIODVALUE, vprev or your inner loop at all. You may need other columns from view_cost_noabc for your insert - if so add them to the select list and the group-by clause. You can then refer to l_arr(i).PREVPERIODVALUE to see the corresponding value, since that's still based on the cursor's rowtype; and I've used a left outer join because it looks (based on the NVL) like you won't always have matching records in LIQUIDATIONSDETAILS.

You haven't shown the table DDL, but since vPROCSESSID is declared as a number it's likely the PROCESSID is also a number - in which case using substr on it is strange - you're doing an implicit conversion to a string, and assuming the length is at least 7 digits. This may make sense for you and your data but just looks odd.

At the moment the forall insert is inside the inner loop which is not likely to be what you really intended; but if you lose the inner loop that problem will go away anyway.