Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 10 Instructions

without comments

Lab #10: Oracle Assignment

Objectives

The lab is designed to teach you how to work with Oracle’s object types, and teach you the concepts of generalization and specialization. Oracle creates object types that support collections with and without methods. Oracle object types without methods have a default constructor like tables, and they do not require the implementation of object bodies. Oracle object types with methods require you to implement object bodies, more or less, object types and bodies act like PL/SQL package specifications and bodies.

  • Learn how to create an object type with methods.
  • Learn how to implement an object body with methods.
  • Learn how to create subtypes.
  • Learn how to create overriding methods that implement generalized invocation, or polymorphism.
  • Learn how to store and use SQL to access polymorphic stored object types.

Business Scenario

Application programming solutions often require the ability to use object types and object subtypes. This lab explores Oracle’s implementation of objects in the database.

Oracle’s choice of implementing object types and subtypes was pioneering and expanded their product from a purely relational database management system (RDBMS) to a object-relational database management system (ORDBMS). There was one slight element that makes using methods of subtypes difficult in SQL. SQL relies on knowing all types before hand, which is formally Interface Definition Language (IDL).

The ability to create object types and subtypes lets you create a table with a single column that can hold an object type instance or any object subtype instances. This gives developers the ability to write complex data to a single source or column. This lets you create a column in a table with a column data type that’s polymorphic (able to assume different related forms). This is an ideal solution when you need to store logging information from different table structures, which is what you’ll do in this lab.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

The following code shows you how to create and test a generalized object type and object body; and a set of object subtypes and object subtype bodies.

  • How to create a general fruit_t object type with default and override constructors.

    The default constructor is actually the one that takes two parameters – oname and name. The override constructor allows you to create an instance without any formal parameters.

    The fruit_t object type declares a fruit_t class. The fruit_t class has two attributes: the oname and name attribute. The parameter names of the constructor function must match the attributes of the fruit_t object class.

    Object types, like package specifications, list function and procedures stubs. Stubs define constructor functions, member functions and procedures, and static functions and procedures.

    Constructor functions let you create an instance of the fruit_t class. While you may declare one or more constructor functions, but you only call one constructor because they’re overloaded. The constructor functions return an instance of the fruit_t class. The instance is known as self, which differs from the this equivalent in the Java programming language.

    Member functions and procedures are also overloaded and they only work on instances of the fruit_t object class. Static functions and procedures work without first creating an instance of the fruit_t class, which means they can’t work with instance attributes like oname and name.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14

    CREATE OR REPLACE
  TYPE fruit_t IS OBJECT
  ( oname VARCHAR2(30)
  , name  VARCHAR2(30)
  , CONSTRUCTOR FUNCTION fruit_t RETURN SELF AS RESULT
  , CONSTRUCTOR FUNCTION fruit_t
    ( oname  VARCHAR2
    , name   VARCHAR2 ) RETURN SELF AS RESULT
  , MEMBER FUNCTION get_name RETURN VARCHAR2
  , MEMBER FUNCTION get_oname RETURN VARCHAR2
  , MEMBER PROCEDURE set_oname (oname VARCHAR2)
  , MEMBER FUNCTION to_string RETURN VARCHAR2)
  INSTANTIABLE NOT FINAL;
/

    You can describe the fruit_t object type with the following syntax, like you do for a table, view, function, procedure, or package:

    DESCRIBE fruit_t

    It prints:

     fruit_t IS NOT FINAL
 Name					   NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION FRUIT_T RETURNS SELF AS RESULT
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION FRUIT_T RETURNS SELF AS RESULT
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

  • How to implement a general fruit_t object type body with default and override constructors.

    You implement a fruit_t object body much like you would implement a fruit_t package body. Object attributes are preceded by the word self and dot (.) because self refers to an instance of the object.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

    CREATE OR REPLACE
  TYPE BODY fruit_t IS
 
    /* Override constructor. */
    CONSTRUCTOR FUNCTION fruit_t RETURN SELF AS RESULT IS
    BEGIN
      self.oname := 'FRUIT_T';
      RETURN;
    END;
 
    /* Formalized default constructor. */
    CONSTRUCTOR FUNCTION fruit_t
    ( oname  VARCHAR2
    , name   VARCHAR2 ) RETURN SELF AS RESULT IS
    BEGIN
      /* Assign an oname value. */
      self.oname := oname;
      self.name := name;
 
      RETURN;
    END;
 
    /* A getter function to return the name attribute. */
    MEMBER FUNCTION get_name RETURN VARCHAR2 IS
    BEGIN
      RETURN self.name;
    END get_name;
 
    /* A getter function to return the name attribute. */
    MEMBER FUNCTION get_oname RETURN VARCHAR2 IS
    BEGIN
      RETURN self.oname;
    END get_oname;
 
    /* A setter procedure to set the oname attribute. */
    MEMBER PROCEDURE set_oname
    ( oname VARCHAR2 ) IS
    BEGIN
      self.oname := oname;
    END set_oname;
 
    /* A to_string function. */
    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
      RETURN '['||self.oname||']';
    END to_string;
  END;
/

    You can create a fruit_t object instance with the following anonymous block. It constructs a local instance of the fruit_t class, and it then prints the default value of the oname attribute. Then, it calls the set_oname instance function to reset the oname instance value and reprints the value of the oname attribute.

    DECLARE
  /* Create a default instance of the object type. */
  lv_instance  FRUIT_T := fruit_t();
BEGIN
  /* Print the default value of the oname attribute. */
  dbms_output.put_line('Default  : ['||lv_instance.get_oname()||']');
 
  /* Set the oname value to a new value. */
  lv_instance.set_oname('SUBSTITUTE');
 
  /* Print the default value of the oname attribute. */
  dbms_output.put_line('Override : ['||lv_instance.get_oname()||']');
END;
/

    It prints:

    DEFAULT  : [FRUIT_T]
Override : [SUBSTITUTE]

  • How to create a table that uses a column with a fruit_t object type.

    You can also store object instances in columns of the database. Oracle stores object instances as flattened objects, which means it stores the constructor call. The constructor call is the object type name followed by a list of parameters in parentheses.

    The following creates a cart table and cart_s sequence to manage fruit_t object instances:

    /* Create logger table. */
CREATE TABLE cart
( cart_id  NUMBER
, item     FRUIT_T );
 
/* Create logger_s sequence. */
CREATE SEQUENCE cart_s;

    You can insert values in the cart table with SQL or in a PL/SQL block. The SQL syntax is:

    INSERT INTO cart
VALUES
( cart_s.NEXTVAL
, fruit_t());
 
INSERT INTO cart
VALUES
( cart_s.NEXTVAL
, fruit_t(
    oname => 'FRUIT_T'
  , name => 'NEW' ));

    The PL/SQL syntax is:

    DECLARE
  /* Declare a variable of the UDT type. */
  lv_fruit  FRUIT_T;
BEGIN
  /* Assign an instance of the variable. */
  lv_fruit := fruit_t(
      oname => 'FRUIT_T'
    , name => 'OLD' );
 
    /* Insert instance of the base_t object type into table. */
    INSERT INTO cart
    VALUES (cart_s.NEXTVAL, lv_fruit);
 
    /* Commit the record. */
    COMMIT;
END;
/

    You can use the following query to verify the INSERT statements:

    COLUMN oname     FORMAT A20
COLUMN get_name  FORMAT A20
COLUMN to_string FORMAT A20
SELECT g.cart_id
,      g.item.oname AS oname
,      NVL(g.item.get_name(),'Unset') AS get_name
,      g.item.to_string() AS to_string
FROM  (SELECT c.cart_id
       ,      TREAT(c.item AS fruit_t) AS item
       FROM   cart c) g
WHERE  g.item.oname = 'FRUIT_T';

    It prints:

      CART_ID  ONAME                GET_NAME             TO_STRING
---------- -------------------- -------------------- --------------------
         1 FRUIT_T              Unset                [FRUIT_T]
         2 FRUIT_T              NEW                  [FRUIT_T]
         3 FRUIT_T              OLD                  [FRUIT_T]

  • How to create an apple_t subtype of the fruit_t object type.

    You can also create any number of subtypes as long as the class above it in the hierarchy doesn’t mark itself as FINAL. Unless you specify FINAL, the default is always NOT FINAL. You can subclass or create a subtype for any NOT FINAL class, like the fruit_t class.

    The following lets you create an apple_t subtype of the fruit_t class. 

    1
2
3
4
5
6
7
8
9
10
11
12
13

    CREATE OR REPLACE
  TYPE apple_t UNDER fruit_t
  ( variety     VARCHAR2(20)
  , class_size  VARCHAR2(20)
  , CONSTRUCTOR FUNCTION apple_t
    ( oname       VARCHAR2
    , name        VARCHAR2
    , variety     VARCHAR2
    , class_size  VARCHAR2 ) RETURN SELF AS RESULT
  , OVERRIDING MEMBER FUNCTION get_name RETURN VARCHAR2
  , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2)
  INSTANTIABLE NOT FINAL;
/

    Line 2 includes the syntax necessary to create an apple_t subtype with the UNDER fruit_t syntax. You should note that PL/SQL uses the UNDER syntax rather than the extends syntax of Java. However, the UNDER syntax effectively extends the fruit_t generalized class.

    You can describe the apple_t subtype the same way you described the fruit_t object type or any table, view, function, procedure, or package:

    DESCRIBE apple_t

    It prints:

     apple_t EXTENDS STUDENT.FRUIT_T
 apple_t IS NOT FINAL
 Name					   NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 VARIETY					    VARCHAR2(20)
 CLASS_SIZE					    VARCHAR2(20)
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION APPLE_T RETURNS SELF AS RESULT
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 NAME				VARCHAR2		IN
 VARIETY			VARCHAR2		IN
 CLASS_SIZE			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    You can also create any number of subtypes as long as the class above it in the hierarchy doesn’t mark itself as FINAL. Unless you specify FINAL, the default is always NOT FINAL. You can subclass or create a subtype for any NOT FINAL class, like the fruit_t class.

    The following lets you create an apple_t subtype of the fruit_t class. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

    CREATE OR REPLACE
  TYPE BODY apple_t IS
 
    /* Default constructor, implicitly available, but you should
       include it for those who forget that fact. */
    CONSTRUCTOR FUNCTION apple_t
    ( oname       VARCHAR2
    , name        VARCHAR2
    , variety     VARCHAR2
    , class_size  VARCHAR2 ) RETURN SELF AS RESULT IS
    BEGIN
      /* Assign inputs to instance variables. */    
      self.oname := oname;
 
      /* Assign a designated value or assign a null value. */
      IF name IS NOT NULL AND name IN ('NEW','OLD') THEN
        self.name := name;
      END IF;
 
      /* Assign inputs to instance variables. */  
      self.variety := variety;
      self.class_size := class_size;
 
      /* Return an instance of self. */
      RETURN;
    END;
 
    /* An overriding function for the generalized class. */
    OVERRIDING MEMBER FUNCTION get_name RETURN VARCHAR2 IS
    BEGIN
      RETURN (self AS fruit_t).get_name();
    END get_name;
 
    /* An overriding function for the generalized class. */
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
      RETURN (self AS fruit_t).to_string()||'.['||self.name||']';
    END to_string;
  END;
/

    Line 37 in the apple_t object body shows you how to perform general invocation. It invokes the fruit_t super type and calls the fruit_t object type’s to_string function and then it concatenates .

  • How to insert an apple_t subtype value into a fruit_t object type column.

    The following inserts an apple_t value in the fruit_t object type column:

    INSERT INTO cart
VALUES
( cart_s.NEXTVAL
, apple_t(
    oname => 'APPLE_T'
  , name => 'NEW' 
  , variety => 'PIPPIN'
  , class_size => 'MEDIUM'));

    You can use the modified query to verify the INSERT statements for both the apple_t and fruit_t object class inserts:

    COLUMN oname     FORMAT A20
COLUMN get_name  FORMAT A20
COLUMN to_string FORMAT A20
SELECT g.cart_id
,      g.item.oname AS oname
,      NVL(g.item.get_name(),'Unset') AS get_name
,      g.item.to_string() AS to_string
FROM  (SELECT c.cart_id
       ,      TREAT(c.item AS fruit_t) AS item
       FROM   cart c) g
WHERE  g.item.oname IN ('FRUIT_T','APPLE_T');

    It prints:

       CART_ID ONAME                GET_NAME             TO_STRING
---------- -------------------- -------------------- --------------------
	 1 FRUIT_T              Unset                [FRUIT_T]
	 2 FRUIT_T              NEW                  [FRUIT_T]
	 3 FRUIT_T              OLD                  [FRUIT_T]
	 4 APPLE_T              NEW                  [APPLE_T].[NEW]

    This has shown you how to store fruit_t generalized object types and apple_t specialized object types in the same object type column. You should note that the to_string function returns a generalized invocation, which means it calls the fruit_t object type function and then adds to return value in the overriding version found in the apple_t subclass.

Students may want or need supplemental articles that let them review tips and techniques. The following is a function that checks for valid dates inside strings. You should run this type of check after you rule out a number and alphanumeric string.

There are two elements to this lab. One creates the base_t object type and a logger table. The logger table creates a logger_id column for the surrogate key column and a log_text column that holds a base_t object type. The other element of the lab creates two subtypes of the base_t object type. The subtypes extend the behavior of the base_t object type.

Lab Description

  1. [10 points] Create the base_t object type, a logger table, and logger_s sequence.

    The sections cover the detailed tasks of the lab:

Instruction Details →

The following qualifies the instructions for this element’s steps:

  • You need to create a base_t object type that maps to the following definition. (Don’t forget that you must drop all dependents before you drop a data type, and you may need to append the FORCE option when you drop object types.) You can display the result by using the following SQL*Plus command at the SQL> prompt: 

    desc base_t

    It will display the following base_t definition:

     base_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION BASE_T RETURNS SELF AS RESULT
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION BASE_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

  • You create a logger table in this step. The logger table has only two columns. One is the logger_id column and it contains a surrogate numeric key. The other log_text column and it uses the base_t object type as its data type.

    You can describe the logger table to verify 

    desc logger

    It should display the following:

     Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOGGER_ID					    NUMBER
 LOG_TEXT					    BASE_T

  • You need to implement a base_t object body. Every instance of the base_t class should do the following:

    • Return the oname result for any instance of the object type when you call the get_oname function
    • Set or reset the oname value for any instance of the object type when you call the set_oname procedure.
    • Return the oname value enclosed in square brackets when you call the to_string function for the base_t object type.
    • Return a null, NEW, or OLD value when you call the get_name function of any base_t object, which means the parameterized constructor must disallow any other value. The NEW or OLD value of the name attribute identifies the copy of the log as the before and after state of an INSERT, UPDATE, or DELETE statement. That exists because you’ll modify this code in the next chapter’s lab on database triggers (see Figure 8 and the transaction discussion on pages 302-303, or Appendix B on data transactions and DML isolation and locking on pages 615-620).

    • Test Case

  • You test that the components work using the following techniques or scripts.

    • You can test the ability to create an instance of the base_t type with a default oname attribute value, set the instance value of the oname attribute to a new value, and get the instance value of the changed oname value with the following anonymous PL/SQL block: 

      DECLARE
  /* Create a default instance of the object type. */
  lv_instance  BASE_T := base_t();
BEGIN
  /* Print the default value of the oname attribute. */
  dbms_output.put_line('Default  : ['||lv_instance.get_oname()||']');
 
  /* Set the oname value to a new value. */
  lv_instance.set_oname('SUBSTITUTE');
 
  /* Print the default value of the oname attribute. */
  dbms_output.put_line('Override : ['||lv_instance.get_oname()||']');
END;
/

      It should print:

      Default  : [BASE_T]
Override : [SUBSTITUTE]

    • You also need to insert a row into the logger table. You can find the syntax to insert into the log_text column on pages 809-811 of the Oracle Database 12c PL/SQL Programming textbook. There are two approaches to inserting the base_t object type because there are two constructors.

      You can insert a base_t object instance with the default no parameter constructor, like

      INSERT INTO logger
VALUES (logger_s.NEXTVAL, base_t());

      You can insert a base_t object instance with the a parameter-driven constructor, like

      DECLARE
  /* Declare a variable of the UDT type. */
  lv_base  BASE_T;
BEGIN
  /* Assign an instance of the variable. */
  lv_base := base_t(
      oname => 'BASE_T'
    , name => 'NEW' );
 
    /* Insert instance of the base_t object type into table. */
    INSERT INTO logger
    VALUES (logger_s.NEXTVAL, lv_base);
 
    /* Commit the record. */
    COMMIT;
END;
/

      After you insert the row, a generic query like this:

      COLUMN oname     FORMAT A20
COLUMN get_name  FORMAT A20
COLUMN to_string FORMAT A20
SELECT t.logger_id
,      t.log.oname AS oname
,      NVL(t.log.get_name(),'Unset') AS get_name
,      t.log.to_string() AS to_string
FROM  (SELECT l.logger_id
       ,      TREAT(l.log_text AS base_t) AS log
       FROM   logger l) t
WHERE  t.log.oname = 'BASE_T';

      Returns the following:

       LOGGER_ID ONAME		GET_NAME	     TO_STRING
---------- -------------------- -------------------- --------------------
	 1 BASE_T               Base_t               [BASE_T]
	 2 BASE_T               NEW                  [BASE_T]

  1. [15 points] Create the item_t and contact_t subtypes of the base_t object type. Then, test the column substitutability in the logger table.

    The sections cover the detailed tasks of the lab:

Instruction Details →

The following qualifies the instructions for this step:

  • You create the item_t subtype by modeling its columns on the item table in the video store code and extending the base_t object type. You exclude any BFILE, BLOB, CLOB, and NCLOB columns from the item_t type definition. You should include overriding methods for the get_name and to_string functions.

    You can display the result by using the following SQL*Plus command at the SQL> prompt:

    desc item_t

    It will display the following:

     item_t extends STUDENT.BASE_T
 item_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 ITEM_ID					    NUMBER
 ITEM_BARCODE					    VARCHAR2(20)
 ITEM_TYPE					    NUMBER
 ITEM_TITLE					    VARCHAR2(60)
 ITEM_SUBTITLE					    VARCHAR2(60)
 ITEM_RATING					    VARCHAR2(8)
 ITEM_RATING_AGENCY				    VARCHAR2(4)
 ITEM_RELEASE_DATE				    DATE
 CREATED_BY					    NUMBER
 CREATION_DATE					    DATE
 LAST_UPDATED_BY				    NUMBER
 LAST_UPDATE_DATE				    DATE
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION ITEM_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 NAME				VARCHAR2		IN
 ITEM_ID			NUMBER			IN
 ITEM_BARCODE			VARCHAR2		IN
 ITEM_TYPE			NUMBER			IN
 ITEM_TITLE			VARCHAR2		IN
 ITEM_SUBTITLE			VARCHAR2		IN
 ITEM_RATING			VARCHAR2		IN
 ITEM_RATING_AGENCY		VARCHAR2		IN
 ITEM_RELEASE_DATE		DATE			IN
 CREATED_BY			NUMBER			IN
 CREATION_DATE			DATE			IN
 LAST_UPDATED_BY		NUMBER			IN
 LAST_UPDATE_DATE		DATE			IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2
  • You need to implement a item_t object body. Every instance of the item_t class should do the following:

    • Return the entire record structure of any instance of the item_t object type.
    • Return the name result for any instance of the object type when you call the get_name function
    • Return the oname value enclosed in square brackets when you call the to_string function for any instance of the item_t object subtype. You implement this by leveraging generalized invocation of the to_string function in the parent class. You should append the name variable’s contents inside square brackets when you override the behavior of the to_string function in the item_t subtype.
    • Return a NEW or OLD value as the name value when you call the get_name function of any item_t object instance.

  • You create the contact_t subtype by modeling its columns on the contact table in the video store code. You exclude any BFILE, BLOB, CLOB, and NCLOB columns from the item_t type definition. You should include overriding methods for the get_name and to_string functions.

    You can display the result by using the following SQL*Plus command at the SQL> prompt:

    desc contact_t

    It will display the following:

     contact_t extends STUDENT.BASE_T
 contact_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 CONTACT_ID					    NUMBER
 MEMBER_ID					    NUMBER
 CONTACT_TYPE					    NUMBER
 FIRST_NAME					    VARCHAR2(60)
 MIDDLE_NAME					    VARCHAR2(60)
 LAST_NAME					    VARCHAR2(8)
 CREATED_BY					    NUMBER
 CREATION_DATE					    DATE
 LAST_UPDATED_BY				    NUMBER
 LAST_UPDATE_DATE				    DATE
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION CONTACT_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 NAME				VARCHAR2		IN
 CONTACT_ID			NUMBER			IN
 MEMBER_ID			VARCHAR2		IN
 CONTACT_TYPE			NUMBER			IN
 FIRST_NAME			VARCHAR2		IN
 MIDDLE_NAME			VARCHAR2		IN
 LAST_NAME			VARCHAR2		IN
 CREATED_BY			NUMBER			IN
 CREATION_DATE			DATE			IN
 LAST_UPDATED_BY		NUMBER			IN
 LAST_UPDATE_DATE		DATE			IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2
  • You need to implement a contact_t object body. Every instance of the contact_t class should do the following:

    • Return the entire record structure of any instance of the contact_t object type.
    • Return the name result for any instance of the object type when you call the get_name function
    • Return the oname value enclosed in square brackets when you call the to_string function for any instance of the contact_t object subtype. You implement this by leveraging generalized invocation of the to_string function in the parent class. You should append the name variable’s contents inside square brackets when you override the behavior of the to_string function in the contact_t subtype.
    • Return a NEW or OLD value as the name value when you call the get_name function of any contact_t object instance.

Test Case

  • You test that the components work using the following techniques or scripts.

    • You need to insert a row into the logger table with a item_t object instance and another row with a contact_t object instance. You can find the syntax to insert into the log_text column in the first section, and on pages 809-811 of the Oracle Database 12c PL/SQL Programming textbook. After you insert the two rows, you can use the following query to verify row inserts: 

      COLUMN oname     FORMAT A20
COLUMN get_name  FORMAT A20
COLUMN to_string FORMAT A20
SELECT t.logger_id
,      t.log.oname AS oname
,      t.log.get_name() AS get_name
,      t.log.to_string() AS to_string
FROM  (SELECT l.logger_id
       ,      TREAT(l.log_text AS base_t) AS log
       FROM   logger l) t
WHERE  t.log.oname IN ('CONTACT_T','ITEM_T');

      Returns the following:

       LOGGER_ID ONAME		GET_NAME	     TO_STRING
---------- -------------------- -------------------- --------------------
	 3 ITEM_T		NEW		     [ITEM_T].[NEW]
	 4 CONTACT_T		NEW		     [CONTACT_T].[NEW]

    Written by michaelmclaughlin

    November 14th, 2016 at 1:42 am

    Posted in