There's been some mentions of this feature on Oracle-base and elsewhere, but here's an example of what excites me.
If you deal with nested arrays/tables in SQL, then you quickly bump into an impedance match. You can't readily get that embedded list into horizontal columns.
I'll use the example I'm most familiar with - 2-D geometry data types.
There's a concept called the MBR (minimum bounding rectangle). You can basically think of it as the most northerly, southerly, easterly and westerly points of an area.
select a.mbr from LGA_2012_AUST a where lga_name12 = 'Darwin (C)';
MBR(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8311, NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(130.815117, -12.469386, 130.938563, -12.33006))
The MBR is a geometry column with an ordinate array listing the four points.
We can do a Collection Unnesting operation, but then we end up with rows.
select b.* from LGA_2012_AUST a, table(a.mbr.sdo_ordinates) b
where lga_name12 = 'Darwin (C)';
COLUMN_VALUE
------------
130.815117
-12.469386
130.938563
-12.33006
To get back to columns, you'd need a PIVOT operation. But because we've lost any sense of the order of those values in the array, that has limitations. In Australia we can easily tell the difference between our latitudes and longitudes, but that isn't possible for many locations.
The WITH operator allows us to extract those array elements easily, and without recourse to a stored function. Though the PRAGMA UDF mention by Tim may mean I don't need to fret about the context switches of switching between SQL and PL/SQL as much.
SQL> l
1 WITH
2 FUNCTION ext_val (i_arr in MDSYS.SDO_ORDINATE_ARRAY, i_val in number) RETURN NUMBER IS
3 BEGIN
4 return i_arr(i_val);
5 END;
6 select ext_val(a.mbr.SDO_ORDINATES,1) v1, ext_val(a.mbr.SDO_ORDINATES,2) v2,
7 ext_val(a.mbr.SDO_ORDINATES,3) v3, ext_val(a.mbr.SDO_ORDINATES,4) v4
8 from LGA_2012_AUST a
9* where lga_name12 = 'Darwin (C)'
SQL> /
V1 V2 V3 V4
---------- ---------- ---------- ----------
130.815117 -12.469386 130.938563 -12.33006
My demo in SQL*Plus works fine in version 12c.
In the 11.2 Instant Client, I could get it to run using
set sqlterminator #
That stops it treating the ";" in line 4 as a terminator, and allows it to pull in the whole statement. The backslash will send it off to the DB for processing, and it works fine then
SQL Developer 4 also seems to choke on the syntax. I'll have to work up the strength to see if it is already logged as an issue. I assume it won't work in 3.2, and that a 12c rollout will require SQL*Plus and SQL Developer installs to be upgraded. Not sure about TOAD and any other clients.
