local collection types not allowed in SQL statements - Oracle

Question

SET SERVEROUTPUT ON;
DECLARE
    type r05_id_type is TABLE  of r01_table.R01_ID%TYPE;

    r01_ids r05_id_type;
BEGIN

SELECT r1.r01_ID as R01_ID
BULK COLLECT INTO r01_ids
FROM r01_table r1, r05_table r5
WHERE r1.r01_ID= r5.r05_R01_ID_FK
AND r5.r05_status='D' AND  r5.r05_date_time_captured <= TRUNC(SYSDATE) - 1095 
AND r1.r01_id NOT IN(select r01.r01_ID
                FROM r01_table r01, r05_table r05
                WHERE r05.r05_status !='D'
                AND r01.r01_ID= r05.r05_R01_ID_FK);

  dbms_output.put_line(r01_ids.COUNT);

  FOR indx IN 1 .. r01_ids.COUNT
  LOOP
     dbms_output.put_line(r01_ids(indx));
     insert all into r01_table_archived (R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)
     values(rr1_R01_ID_TYPE,rr1_R01_IDENTITY_NUMBER,rr1_R01_PASSPORT_COUNTRY,rr1_R01_DATE_TIME_CAPTURED)
     select rr1_R01_ID_TYPE,rr1_R01_IDENTITY_NUMBER,rr1_R01_PASSPORT_COUNTRY,rr1_R01_DATE_TIME_CAPTURED
     FROM (SELECT R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED
            FROM r01_table
            WHERE r01_id IN (r01_ids));
  END LOOP;

END;

I am writing the above procedure in order to check some records between two tables and storing the matching ID (r01_ID) in an inner table call r01_ids. dbms_output.put_line(r01_ids(indx)) prints the correct values which indicates that the values are correctly inserting to r01_ids. but the issue happens when I try to insert the values to the r01_table_archived table within the LOOP.

I am getting the below error

PLS-00642: local collection types not allowed in SQL statements

Can't I get the values of r01_ids from the insert query like this? If I am wrong, someone please help me to find what's the issue and how to perform it in the right way.

Answer 1

Until Oracle 11g, if you declare type under the scope of a PLSQL block, it cannot be called in SQL statement within the block and this is the reason you are getting this error:

PLS-00642: local collection types not allowed in SQL statements

You can create a type outside the SCOPE of PLSQL block and then use it in SQL. See below:

--Created a type of Number assuming ID is number
CREATE OR REPLACE TYPE R05_ID_TYPE IS TABLE OF NUMBER;
/
--Block
DECLARE
    --TYPE R05_ID_TYPE IS TABLE OF R01_TABLE.R01_ID%TYPE;

     R01_IDS                       R05_ID_TYPE;
BEGIN
     SELECT R1.R01_ID AS R01_ID
     BULK COLLECT INTO R01_IDS
       FROM R01_TABLE R1,
            R05_TABLE R5
      WHERE R1.R01_ID = R5.R05_R01_ID_FK
            AND R5.R05_STATUS = 'D'
            AND R5.R05_DATE_TIME_CAPTURED <= TRUNC (SYSDATE) - 1095
            AND R1.R01_ID NOT IN (
                    SELECT R01.R01_ID
                      FROM R01_TABLE R01,
                           R05_TABLE R05
                     WHERE R05.R05_STATUS != 'D'
                           AND R01.R01_ID = R05.R05_R01_ID_FK);

     DBMS_OUTPUT.PUT_LINE (R01_IDS.COUNT);

     FOR INDX IN 1 .. R01_IDS.COUNT
     LOOP
          DBMS_OUTPUT.PUT_LINE (R01_IDS (INDX) );
          INSERT 
                INTO R01_TABLE_ARCHIVED
                     (R01_ID_TYPE,
                      R01_IDENTITY_NUMBER,
                      R01_PASSPORT_COUNTRY,
                      R01_DATE_TIME_CAPTURED)
               SELECT RR1_R01_ID_TYPE,
                      RR1_R01_IDENTITY_NUMBER,
                      RR1_R01_PASSPORT_COUNTRY,
                      RR1_R01_DATE_TIME_CAPTURED
                 FROM (SELECT R01_ID_TYPE,
                              R01_IDENTITY_NUMBER,
                              R01_PASSPORT_COUNTRY,
                              R01_DATE_TIME_CAPTURED
                         FROM R01_TABLE
                        --Using the collection in SQL statement
                        WHERE R01_ID IN (Select column_value from table(R01_IDS) );
     END LOOP;
COMMIT;
END;