Type alias for user-defined type in select statement : Nested Tables « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Nested Tables

SQL>
SQL>
SQL>
SQL>
SQL> create table courses
  2  ( code        VARCHAR2(6)
  3  , description VARCHAR2(30)
  4  , category    CHAR(3)
  5  , duration    NUMBER(2)) ;

Table created.

SQL>
SQL>
SQL> insert into courses values('SQL','SQL course',    'GEN',4);

1 row created.

SQL> insert into courses values('OAU','Oracle course', 'GEN',1);

1 row created.

SQL> insert into courses values('JAV','Java course',   'BLD',4);

1 row created.

SQL> insert into courses values('PLS','PL/SQL course', 'BLD',1);

1 row created.

SQL> insert into courses values('XML','XML course',    'BLD',2);

1 row created.

SQL> insert into courses values('ERM','ERM course',    'DSG',3);

1 row created.

SQL> insert into courses values('PMT','UML course',    'DSG',1);

1 row created.

SQL> insert into courses values('RSD','C# course',     'DSG',2);

1 row created.

SQL> insert into courses values('PRO','C++ course',    'DSG',5);

1 row created.

SQL> insert into courses values('GEN','GWT course',    'DSG',4);

1 row created.

SQL>
SQL>
SQL> create type errorNumberType as object
  2  ( code varchar2(4)
  3  , ch   number(2)
  4  , pg   number(3)
  5  , txt  varchar2(40)
  6  ) ;
  7  /

Type created.

SQL>
SQL> create type errorNumberTableCollection as table of errorNumberType;
  2  /

Type created.

SQL>
SQL> create table c as select * from courses;

Table created.

SQL>
SQL> alter table c
  2  add (errata errorNumberTableCollection)
  3  nested table errata store as errata_tab;

Table altered.

SQL>
SQL> update c set errata = errorNumberTableCollection();

10 rows updated.

SQL>
SQL>
SQL> insert into table ( select errata from c where  code = 'SQL' )
  2  values ( 'SQL', 3, 45, 'line' );

1 row created.

SQL>
SQL>
SQL> set linesize 70
SQL> break on row page
SQL>
SQL>
SQL>
SQL> set numwidth 3
SQL> col code format a7
SQL> col ch fold_before
SQL>
SQL> select code,c.description, e.ch, e.pg, e.txt
  2  from   c
  3         join
  4         table(c.errata) e
  5         using (code);

CODE    DESCRIPTION
------- ------------------------------
    CH     PG TXT
------ ------ ----------------------------------------
SQL     SQL course
     3     45 line


1 row selected.

SQL>
SQL> clear breaks
breaks cleared
SQL>
SQL>
SQL> drop table courses;

Table dropped.

SQL>
SQL> drop type errorNumberTableCollection force;

Type dropped.

SQL> drop type errorNumberType    force;

Type dropped.

SQL>