Describe table with user-defined type as column type : Describe Type « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. Describe Type

SQL>
SQL> create table course_schedule
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , trainer    NUMBER(4)
  5  , location   VARCHAR2(20)) ;

Table created.

SQL>
SQL>
SQL> insert into course_schedule values ('SQL',date '1999-04-12',1,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('OAU',date '1999-08-10',2,'CHICAGO');

1 row created.

SQL> insert into course_schedule values ('SQL',date '1999-10-04',3,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('SQL',date '1999-12-13',4,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('JAV',date '1999-12-13',5,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('XML',date '2000-02-03',6,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('JAV',date '2000-02-01',7,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('PLS',date '2000-09-11',8,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('XML',date '2000-09-18',NULL,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('OAU',date '2000-09-27',9,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('ERM',date '2001-01-15',10, NULL    );

1 row created.

SQL> insert into course_schedule values ('PRO',date '2001-02-19',NULL,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('RSD',date '2001-02-24',8,'CHICAGO');

1 row created.

SQL>
SQL>
SQL>
SQL> create type addressType as object
  2  ( street varchar2(20)
  3  , nr     varchar2(5)
  4  , pcode  varchar2(6)
  5  , city   varchar2(20)
  6  ) ;
  7  /

Type created.

SQL>
SQL> describe addressType
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STREET                                             VARCHAR2(20)
 NR                                                 VARCHAR2(5)
 PCODE                                              VARCHAR2(6)
 CITY                                               VARCHAR2(20)

SQL>
SQL> select type_name, typecode from user_types;

TYPE_NAME                      TYPECODE
------------------------------ ------------------------------
DEBUG_O                        OBJECT
NUMBERLIST_T                   COLLECTION
ADDRESS2                       OBJECT
ZIP_CODE                       OBJECT
NUM_VARRAY                     COLLECTION
NUM_TABLE                      COLLECTION
ADD_LIST                       COLLECTION
HOME_ADD_LIST                  COLLECTION
SYS_PLSQL_14797_54_1           COLLECTION
SYS_PLSQL_14797_DUMMY_1        COLLECTION
SYS_PLSQL_14797_9_1            OBJECT
VCARRAY                        COLLECTION
STRINGS_NT                     COLLECTION
RIVER                          OBJECT
WATERFALL                      OBJECT
SQLMONTH_TABLETYPE             COLLECTION
ORD_TYPE                       OBJECT
ORD_TABLE                      COLLECTION
DTARRAY                        COLLECTION

TYPE_NAME                      TYPECODE
------------------------------ ------------------------------
NMARRAY                        COLLECTION
EMP_T                          OBJECT
ASCII_TABLE_TYPE               OBJECT
PHONE_NUM_REC_TYPE             OBJECT
NUMBERVARRAY                   COLLECTION
ADDRESSTYPE                    OBJECT

25 rows selected.

SQL>
SQL> create table o
  2  as
  3  select course, begindate, trainer
  4  from   course_schedule;

Table created.

SQL>
SQL> alter table o add (address addressType);

Table altered.

SQL>
SQL> set describe depth 1
SQL> describe o
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COURSE                                             VARCHAR2(6)
 BEGINDATE                                          DATE
 TRAINER                                            NUMBER(4)
 ADDRESS                                            ADDRESSTYPE

SQL> set describe depth 2
SQL> describe o
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COURSE                                             VARCHAR2(6)
 BEGINDATE                                          DATE
 TRAINER                                            NUMBER(4)
 ADDRESS                                            ADDRESSTYPE
   STREET                                           VARCHAR2(20)
   NR                                               VARCHAR2(5)
   PCODE                                            VARCHAR2(6)
   CITY                                             VARCHAR2(20)

SQL>
SQL> drop view o;
drop view o
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table o;

Table dropped.

SQL> drop type addressType force;

Type dropped.

SQL> drop table course_schedule;

Table dropped.

SQL>
SQL>

SQL>