1 Reply Latest reply: Feb 2, 2013 11:19 PM by jeneesh RSS

    Collections::

    983563
      I am weak in collections please expain me what this cast( multiset( selec function will do:::

      ops$tkyte@ORA817DEV.US.ORACLE.COM> create type empType as object
        2  ( ename varchar2(30),
        3    empno number
        4  )
        5  /
      Type created.
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> create type empTblType as table of empType
        2  /
      Type created.
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> create type projType as object
        2  ( name     varchar2(30),
        3    start_date date,
        4    end_date   date
        5  )
        6  /
      Type created.
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> create type projTblType as table of projType
        2  /
      Type created.
      
      
      Now I need some data to query:
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;
      Table created.
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> create table dept as select * from scott.dept;
      Table created.
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> create table proj as
        2  select username name,
        3         created start_date,
        4         created+7 end_date,
        5         (mod(rownum,4)+1)*10 deptno
        6    from all_users
        7   where rownum <= 20
        8  /
      Table created.
      
      And now we can query the data:
      
      ops$tkyte@ORA817DEV.US.ORACLE.COM> select dname,
        2   cast( multiset( select ename, empno
        3                     from emp
        4                    where emp.deptno = dept.deptno ) as empTblType ) emps,
        5   cast( multiset( select name, start_date, end_date
        6                     from proj
        7                    where proj.deptno = dept.deptno ) as projTblType ) projs
        8    from dept
        9  /
      DNAME          EMPS(ENAME, EMPNO)             PROJS(NAME, START_DATE, END_DA
      -------------- ------------------------------ ------------------------------
      ACCOUNTING     EMPTBLTYPE(EMPTYPE('CLARK', 77 PROJTBLTYPE(PROJTYPE('DBSNMP',
                     82), EMPTYPE('KING', 7839), EM  '28-AUG-01', '04-SEP-01'), PR
                     PTYPE('MILLER', 7934))         OJTYPE('OPS$TKYTE', '24-SEP-01
                                                    ', '01-OCT-01'))
      
      RESEARCH       EMPTBLTYPE(EMPTYPE('A', 7369), PROJTBLTYPE(PROJTYPE('SYS', '2
                      EMPTYPE('JONES', 7566), EMPTY 8-AUG-01', '04-SEP-01'), PROJT
                     PE('SCOTT', 7788), EMPTYPE('AD YPE('OPS$ORA817', '28-AUG-01',
                     AMS', 7876), EMPTYPE('FORD', 7  '04-SEP-01'), PROJTYPE('OPS$C
                     902))                          LBECK', '09-SEP-01', '16-SEP-0
                                                    1'))
      
      SALES          EMPTBLTYPE(EMPTYPE('ALLEN', 74 PROJTBLTYPE(PROJTYPE('SYSTEM',
                     99), EMPTYPE('WARD', 7521), EM  '28-AUG-01', '04-SEP-01'), PR
                     PTYPE('MARTIN', 7654), EMPTYPE OJTYPE('SCOTT', '28-AUG-01', '
                     ('BLAKE', 7698), EMPTYPE('TURN 04-SEP-01'), PROJTYPE('A', '20
                     ER', 7844), EMPTYPE('JAMES', 7 -SEP-01', '27-SEP-01'))
                     900))
      
      OPERATIONS     EMPTBLTYPE()                   PROJTBLTYPE(PROJTYPE('OUTLN',
                                                    '28-AUG-01', '04-SEP-01'), PRO
                                                    JTYPE('CTXSYS', '28-AUG-01', '
                                                    04-SEP-01'))
        • 1. Re: Collections::
          jeneesh
          It is very clearly written in the Docs

          MULTISET here, converts a query result set to a collection value.

          CAST converts a collection type to another one, here to your empTblType. So you are getting a collection output..