Oracle table () function usage

Oracle uses the table () function to replace the result set returned by PL/SQL.

1. table () combined with Arrays:

Create or replace type t_test as object (
Id integer,
Rq date,
Mc varchar2 (60)
);

Create or replace type t_test_table as table of t_test;

Create or replace function f_test_array (n in number default null) return t_test_table
As
V_test t_test_table: = t_test_table ();
Begin
For I in 1 .. nvl (n, 100) loop
V_test.extend ();
V_test (v_test.count): = t_test (I, sysdate, 'mc '| I );
End loop;
Return v_test;
End f_test_array;
/

Select * from table (f_test_array (10 ));

Select * from the (select f_test_array (10) from dual );

2. table () combined with the PIPELINED function:

Create or replace function f_test_pipe (n in number default null) return t_test_table PIPELINED
As
V_test t_test_table: = t_test_table ();
Begin
For I in 1 .. nvl (n, 100) loop
Pipe row (t_test (I, sysdate, 'mc '| I ));
End loop;
Return;
End f_test_pipe;
/

Select * from table (f_test_pipe (20 ));

Select * from the (select f_test_pipe (20) from dual );

3. table () combined with system package:
Create table test (id varchar2 (20 ));
Insert into test values ('1 ');
Commit;
Explain plan for select * from test;
Select * from table (dbms_xplan.display );

***************************************