One of the new things in Oracle12c is, that more (compared to Oracle11g) pure PL/SQL types
are accessible from SQL queries or DML statements. Pure PL/SQL types are record-, boolean
or "INDEX BY" types. The latter are also called associative arrays. So, beginning with 12.1,
we can access PL/SQL INDEX BY tables with a SQL query - according to
the documentation. This blog posting describes how it works (and what does not work).

First, the setup. We need (of course) a PL/SQL function returing a PL/SQL INDEX BY table.
And in order to create such a function, we need to have a package specification with the
INDEX BY table definition. To make it easy, we put the function into the package as well.
Reading the documentation carefully, we observe the first restriction for this feature:
The INDEX BY table must be declared as INDEX BY PLS_INTEGER.

Does not work. Bummer. OK, read the documentation again. Aah, there is the other
restriction: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL.
But what's the value of that new feature then ...? And how can it be used?

So, PL/SQL INDEX BY tables are accessible for a SQL statement
within a PL/SQL procedure, function or anonymous block (use the TABLE() operator).
This can be very useful: think about joining your PL/SQL INDEX BY table to a
database table, as follows ...

In Oracle 12.1, PL/SQL and SQL work more closely together. We can use INDEX BY tables within
SQL queries now, as long as this query is being executed within a PL/SQL context. So
INDEX BY tables are still PL/SQL, not SQL data types. We still have some barriers
for PL/SQL data types in SQL - but the development direction is good.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.