Does Oracle 12 have problems with local collection types in SQL?

In further experiments we found out that problems are even deeper than it's been assumed.

For example, varying elements used in the package buggy_report we can get an ORA-03113: end-of-file on communication channelwhen running the script (in the question). It can be done with changing the type of t_id_table to VARRAY or TABLE .. INDEX BY ... There are a lot of ways and variations leading us to different exceptions, which are off topic to this post.

The one more interesting thing is that compilation time of buggy_report package specification can take up to 25 seconds,when normally it takes about 0.05 seconds. I can definitely say that it depends on presence of TYPE t_id_table parameter in the pipe_table function declaration, and "long time compilation" happen in 40% of installation cases. So it seems that the problem with local collection types in SQL latently appear during the compilation.

So we see that Oracle 12.1.0.2 obviously have a bug in realization of using local collection types in SQL.

The minimal examples to get ORA-22163 and ORA-03113 are following. There we assume the same buggy_report package as in the question.

-- produces 'ORA-03113: end-of-file on communication channel'DECLARE     l_cur buggy_report.t_info_cur;  FUNCTION get_it RETURN buggy_report.t_info_cur IS BEGIN RETURN buggy_report.get_cursor(); END;    BEGIN   l_cur := get_it();   dbms_output.put_line('');END;/-- produces 'ORA-22163: left hand and right hand side collections are not of same type'DECLARE    l_cur buggy_report.t_info_cur;  PROCEDURE hello IS BEGIN NULL; END;BEGIN  l_cur := buggy_report.get_cursor;  -- comment `hello` and exception disappears  hello;  CLOSE l_cur;END;/

Yes, in Oracle 12c you are allowed to use local collection types in SQL.

Documentation Database New Features Guide says:

PL/SQL-Specific Data Types Allowed Across the PL/SQL-to-SQL Interface

The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection's data type had to be declared at the schema level.)

However, I don't know why your code is not working, maybe this new feature has still a bug.

I fiddled around your example. The trick how Oracle 12c can use PL/SQL collections in SQL statements is that Oracle creates surrogate schema object types with compatible SQL type attributes and uses these surrogate types in a query. Your case looks like a bug. I traced the execution and the surrogate types are created only once if not exist. So the effective type doesn't change nor recompile (don't know if implicit recompilation are done using ALTER statement) during execution of pipelined function. And the issue only occurs if you use the p parameter in pipe_table function. If you don't call l_table := p; the code executes successfully even with enabled method call.