Lab 10:  User-Defined Types

In this lab, you will create an object table containing client data. Each row will contain attributes for a client:  client number, client name and a nested table of contact numbers.
  1. Create an object type called CONTACT_OBJ that will contain contact numbers (telephone, fax, pager etc).  CONTACT_OBJ should contain the following attributes:

    2.        CONTACT_TYPE         CHAR(10)
           CONTACT_NUMBER  CHAR(12)
     

  2. Create a nested table of CONTACT_OBJs called CONTACT.

    3.  
  3. Create an object type called CLIENT_OBJ with the following attributes:

    4.        CLIENT_NO                 CHAR(2)
           CLIENT_NAME           CHAR(15)
           CONTACT_NUMS      CONTACT

    (Optional) Include a method called FAX that will return the FAX number for a client.  Run the script file fax.sql to create the FAX method body.
     

  4. Create an object table called CLIENT_TBL containing CLIENT_OBJs.

    5.  
  5. Insert the following data into the client table:

    6.       12  Smith   Contact Numbers:  Telephone    444-6666
                                                          Fax             333-9999
           19 Jones   Contact Numbers:   Telephone   101-1001
                                                          Pager           222-2020
                                                          Cell Phone   345-6789
     

  6. Display the data in CLIENT_TBL.

    7.  
  7. (Optional) Test the FAX method using the following select statement:

    8.            select client_no, c.fax() from client_tbl c;