Mega Code Archive
Type alias for user-defined type in select statement
<![CDATA[
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> 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> 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> insert into table ( select errata from c where code = 'SQL' )
2 values ( 'SQL', 3, 45, 'line' );
1 row created.
SQL>
SQL> set linesize 70
SQL> break on row page
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> drop table courses;
Table dropped.
SQL>
SQL> drop type errorNumberTableCollection force;
Type dropped.
SQL> drop type errorNumberType force;
Type dropped.
SQL>
]]>