Function based tables

You've probably seen this somewhere already, no doubt from Connor, though I couldn't find much beyond Tim's post on pipelined functions - I can't find the right keywords to find related content.

I like table functions, so this will help me remember we no longer need to specify the table() operator in 18c (12.2).

12c

select * 
from table(
  apex_string.split('A,B,C',',')
);

Result Sequence                                                                                                                      
---------------
A
B
C

12c> select * from apex_string.split('A,B,C',',');

ORA-00933: SQL command not properly ended

18c

select * from apex_string.split('A,B,C',',');

Result Sequence                                                                                                                      
---------------
A
B
C

This apex_string package is like the swiss army knife of string manipulation. I love it.
I think I'm surprised a few other posts haven't made it out of draft. This example felt a little like butchery, but it was an interesting play.

Here's a way to test it out with your own function, returning a supplied APEX collection type - a table/collection of strings.

create or replace function tf_test return apex_t_varchar2 is
  lt   apex_t_varchar2 := apex_t_varchar2(); -- ORA-06531: Reference to uninitialized collection
begin 
 for i in 1..12 loop
   lt.extend; -- ORA-06533: Subscript beyond count
   lt(lt.last) := add_months(trunc(sysdate,'yy'),i-1);
 end loop;

  return (lt);
end;
/ 

select column_value as dt 
from tf_test();

DT
---------
01/JAN/19
01/FEB/19
01/MAR/19
01/APR/19
01/MAY/19
01/JUN/19
01/JUL/19
01/AUG/19
01/SEP/19
01/OCT/19
01/NOV/19
01/DEC/19

12 rows selected

If you don't include the section with comments, you get the relevant error.

We need the trailing brackets even in the absence of actual parameters. We didn't in the old format, but I couldn't find the updated syntax diagram.

select * from tf_test;
-- ORA-04044: procedure, function, package, or type is not allowed here

We can subtract more code, just not the brackets ;p