CODEWITHSUNDEEP
oracle-databaseplsqlcursorsys-refcursor
Young Al Capone
Young Al Capone

Reputation: 399

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!!!

Upvotes: 0

Views: 277

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Sure, as long as the collection type is known to SQL:

create or replace type number_tt as table of number;

(or use an existing type - query all_coll_types to see what is already defined.)

Now you can use your number_tt in SQL, for example:

select number_tt(1,2,3) as my_numbers from dual;

so if your function returns number_tt there's no problem:

create or replace function numberwang
    ( p1 number, p2 number, p3 number )
    return number_tt
as
begin
    return number_tt(p1,p2,p3);
end numberwang;
/

select numberwang(23,42,96) as my_numbers from dual;

This can be part of a ref cursor, returned from a procedure etc as normal.

Upvotes: 0

Stew Ashton
Stew Ashton

Reputation: 1529

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    {}

Upvotes: 2

Related Questions