Is there any way to add a plsql table as column in SELECT clause from a cursor?

I'm coding a plsql procedure that return a SYS_REFCURSOR CURSOR.

  PROCEDURE PRC_USER_APPS(o_total_rows    OUT NUMBER,
                          o_result_status OUT VARCHAR2,
                          o_cursor        OUT SYS_REFCURSOR);

I'm stuck with my current assignment because I need to add a plsql table or nested table returned from a function as a column in the SELECT cursor. Is possible to code something like this?

OPEN o_cursor FOR
  SELECT id_application, usr.id_user, name_user, fnc_user_phone(usr.id_user)
  FROM   users usr,
         users_applications uap
  WHERE  usr.id_user = uap.id_user;

Result:

99909, 001, 'José de San Martín', {'mobilephone1', 549351999999, 'mobilephone2', 54935188888}

happy New Year!!!

Solution

You need to understand what is meant by "plsql table" or "nested table".

The Oracle documentation calls this kind of thing "collections". You have to define a collection TYPE, then make a collection of that type.

There are SQL collection types and PL/SQL collection types.

SQL collection types come from CREATE TYPE ... AS TABLE statements
PL/SQL collection types are defined within PL/SQL code with TYPE ... IS TABLE statements.
SQL statements (like SELECT) only understand SQL collection types!
PL/SQL code can use SQL collection types.
SQL collections can be VARRAYs or nested tables.

In your case, you simply cannot use a "plsql table" in a SELECT statement, but you can use a "nested table" if it is based on a SQL type.

create or replace type t_phone as object(
  label varchar2(20),
  nbr integer
)
/
create or replace type tt_phone as table of t_phone
/
select d.*, tt_phone(t_phone('home',12345678), t_phone('work',23456789))
from dept d;

You will have trouble seeing the output because of these SQL types. Also, whoever gets your ref cursor will have to understand your types too. This is not obvious.

You can make things easier by using a CURSOR instead of a nested table:

select d.*, cursor(select empno, ename from emp where deptno = d.deptno) emps
from dept d;

DEPTNO DNAME        LOC       EMPS
    10 ACCOUNTING   NEW YORK  {<EMPNO=7782,ENAME=CLARK>,<EMPNO=7839,ENAME=KING>,<EMPNO=7934,ENAME=MILLER>,}
    20 RESEARCH     DALLAS    {<EMPNO=7369,ENAME=SMITH>,<EMPNO=7566,ENAME=JONES>,<EMPNO=7788,ENAME=SCOTT>,<EMPNO=7876,ENAME=ADAMS>,<EMPNO=7902,ENAME=FORD>,}
    30 SALES        CHICAGO   {<EMPNO=7499,ENAME=ALLEN>,<EMPNO=7521,ENAME=WARD>,<EMPNO=7654,ENAME=MARTIN>,<EMPNO=7698,ENAME=BLAKE>,<EMPNO=7844,ENAME=TURNER>,<EMPNO=7900,ENAME=JAMES>,}
    40 OPERATIONS   BOSTON    {}