PL/SQL block is throwing error- PLS-00382: expression is of wrong type

Question

Logical steps that I am trying to achieve inside a PL/SQL block

1. Create a type of table referencing to row of a table
2. Collect rows from the table into the table variable
3. Loop through the table variable and print specific column as needed.

    DECLARE
    TYPE my_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
    l_arr_type  my_type;
    
    BEGIN
        SELECT * BULK COLLECT INTO l_arr_type FROM emp;
    FOR rec IN (SELECT * FROM TABLE (l_arr_type) ORDER BY HIREDATE)
            LOOP
               DBMS_OUTPUT.PUT_LINE('Ename is: '||rec.ENAME||' and hire date is: '||rec.HIREDATE);
            END LOOP; 
    END;

Error:

ORA-06550: line 8, column 38: PLS-00382: expression is of wrong type

But when i am putting same logic with a package it is working fine below is the code-snippet

    CREATE OR REPLACE PACKAGE my_array IS
     TYPE my_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
    END my_array;
    /
    
    DECLARE
    l_arr_type my_array.my_type;
    
    BEGIN
        SELECT * BULK COLLECT INTO l_arr_type FROM emp;
    
        FOR rec IN (SELECT * FROM TABLE (l_arr_type) ORDER BY HIREDATE)
            LOOP
               DBMS_OUTPUT.PUT_LINE('Ename is: '||rec.ENAME||' and hire date is: '||rec.HIREDATE);
            END LOOP;    
    END;

It will be helpful if this behavior can be clarified, thanking you in advance.

Answer 1

SQL is different than PL/SQL. They are processed by different engines and have a different set of recognized types. When you invoke a SQL statement within PL/SQL, you switch to the SQL engine and it operates without visibility to your code. For this reason, historically SQL could only navigate collections typed as SQL types (create type ...). PL/SQL types were inaccessible to it.

But starting in 12.1, Oracle began permitting the TABLE SQL operator to access PL/SQL collections, but requires them to be defined in package headers. Package headers are sort of like exposed object specifications that outside code, and now the SQL engine itself, can interrogate to understand how to access a PL/SQL object, including a collection.