title: local collection types not allowed in SQL statements - Oracle
body: <pre><code>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 &lt;= 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;
  </code></pre>
  
  <p>I am writing the above procedure in order to check some records between two tables and storing the matching ID (<code>r01_ID</code>) in an inner table call <code>r01_ids</code>. 
  <code>dbms_output.put_line(r01_ids(indx))</code> prints the correct values which indicates that the values are correctly inserting to <code>r01_ids</code>.
  but the issue happens when I try to insert the values to the  r01_table_archived table within the LOOP.</p>
  
  <p>I am getting the below error</p>
  
  <blockquote>
    <p>PLS-00642: local collection types not allowed in SQL statements</p>
  </blockquote>
  
  <p>Can't I get the values of <code>r01_ids</code> 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.</p>