Hi,
I understand in Oracle 12c, the TABLE operator can now be used in PL/SQL with locally defined types. In previous releases, the TABLE operator would only work with locally defined types if they were used within pipelined table functions.
The removal of this restriction means this functionality is available for use with associative arrays, as well as nested tables and varrays in PL/SQL.
I have used this concept widely in my new codes and it is easier to check/retrieve data from an array with simple code, but now i am facing issues with one scenario ,
please find the below examples.
CREATE OR REPLACE PACKAGE test_nested_table IS
TYPE check_row_id IS RECORD
(
unique_id VARCHAR2(100),
l_rowid rowid
);
TYPE check_row_id_array IS TABLE OF check_row_id;
l_check_row_id_array check_row_id_array;
END;
create or replace package body test_nested_table is
PROCEDURE chk_table IS
BEGIN
FOR rec IN ( SELECT *
FROM TABLE (l_check_row_id_array)
)
LOOP
DBMS_OUTPUT.put_line (rec.l_rowid);
END LOOP;
END;
end test_nested_table;
When i try to compile it returns below error
Error: PLS-00382: expression is of wrong type
Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Error: PL/SQL: SQL Statement ignored
Error: PLS-00364: loop index variable 'REC' use is invalid
Error: PL/SQL: Statement ignored
This is because of the type i created with a column having rowid as datatype, if i change the datatype then it works , does any one having a workaround or a known solution for this issue ?