Database Tutorial

Course Tutorial Site

Site Admin

CIT 325 : Final Project

without comments

Final: Oracle Assignment

Objectives

The final lab teaches you how to work Oracle object types and subtypes, and how to structure solutions to problems in modules. You will learn the following:

  • Learn how to create and run modules as independent units.
  • Learn how to create and run integration testing with independent program units.
  • Learn how to package a solution in a Bash shell.

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).

IDL is key to remote method invocation in Java. It is also the foundations for scalable application development between different servers. IDL requires all components have a copy of all possible method signatures. These are often called “stubs” because they provide the method signature without the implementation, like PL/SQL packages define functions and procedures.

The SQL engine needs a get_name() method in the base_t object type because subtypes will include it. SQL can only find the subtype get_name() method when a stub for it is available in the generalized base_t class.

Help Section

The help section shows you how to create a base type and subtype. It demonstrates overriding member functions and procedures, and how to implement generalized invocation in the to_string member function.

Instructional Material →

  • The order_comp object type mirrors the base object type from Chapter 11. The following declares the order_comp object type:

    CREATE OR REPLACE
  TYPE order_comp IS OBJECT
  ( who   VARCHAR2(20)
  , movie VARCHAR2(20)
  , CONSTRUCTOR FUNCTION order_comp
    ( who    VARCHAR2
    , movie  VARCHAR2 )
    RETURN SELF AS RESULT
  , MEMBER FUNCTION to_string RETURN VARCHAR2
  , ORDER MEMBER FUNCTION equals
    ( object order_comp ) RETURN NUMBER )
  INSTANTIABLE NOT FINAL;
/

    You implement the order_comp object type body with the following code:

    CREATE OR REPLACE
  TYPE BODY order_comp IS
  /* Implement a default constructor. */
  CONSTRUCTOR FUNCTION order_comp
  ( who    VARCHAR2
  , movie  VARCHAR2 )
  RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    self.movie := movie;
    RETURN;
  END order_comp;
 
  /* Implement a to_string function. */
  MEMBER FUNCTION to_string
  RETURN VARCHAR2 IS
  BEGIN
    RETURN '['||self.movie||']['||self.who||']';
  END to_string;
 
  /* Implement an equals function. */
  ORDER MEMBER FUNCTION equals
  ( object  ORDER_COMP ) RETURN NUMBER IS
  BEGIN
    IF self.movie < object.movie THEN
      RETURN 1;
    ELSIF self.movie = object.movie AND self.who < object.who THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END equals;
END;
/

    You should put the order_comp and object type and type body into a single order_comp.sql file. After you create the order_comp.sql file, you should run it to drop the order_comp type and any dependents before you try to create the order_comp object type and body.

    You can test the order_comp object type with the following anonymous block program:

    SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  lv_order  ORDER_COMP := order_comp('Clark Kent','Superman');
BEGIN
  dbms_output.put_line(lv_order.to_string);
END;
/

    It returns:

    [Superman][Clark Kent]

  • The order_subcomp object type mirrors the base object type from Chapter 11. The following declares the order_subcomp object type:

    CREATE OR REPLACE
  TYPE order_subcomp UNDER order_comp
  ( subtitle  VARCHAR2(20)
  , CONSTRUCTOR FUNCTION order_subcomp
  ( who       VARCHAR2
  , movie     VARCHAR2
  , subtitle  VARCHAR2)
  RETURN SELF AS RESULT
  , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 )
  INSTANTIABLE FINAL;
/

    You implement the order_subcomp object type body with the following code:

    CREATE OR REPLACE TYPE BODY order_subcomp IS
  /* Implement a default constructor. */  
  CONSTRUCTOR FUNCTION order_subcomp
  ( who       VARCHAR2
  , movie     VARCHAR2
  , subtitle  VARCHAR2)
  RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    self.movie := movie;
    self.subtitle := subtitle;
    RETURN;
  END order_subcomp;
 
  /* Implement an overriding to_string function with
     generalized invocation. */
  OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN (self AS order_comp).to_string||'['||self.subtitle||']';
  END to_string;
END;
/

    You should put the order_subcomp and object type and type body into a single order_subcomp.sql file. After you create the order_subcomp.sql file, you should run it to drop the order_subcomp type and any dependents before you try to create the order_subcomp object type and body.

    You can test the order_subcomp object type with the following anonymous block program:

    SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  lv_order  ORDER_COMP := order_subcomp('Clark Kent','Superman','The Quest for Peace');
BEGIN
  dbms_output.put_line(lv_order.to_string);
END;
/

    It returns:

    [Superman][Clark Kent][The Quest FOR Peace]

  • You create an order_comp.sql file, an order_subcomp.sql file, a check_order.sql file, and an order_compset.sh Bash shell script.

    You create an order_comp.sql file that does the following three things:

    • Create the order_comp object type.
    • Create the order_comp object type body.
    • Add a QUIT; statement.

    CREATE OR REPLACE
  TYPE order_comp IS OBJECT
  ...
  INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE
  TYPE BODY order_comp IS
  ...
  END;
/
 
QUIT;

    You create an order_subcomp.sql file that does the following three things:

    • Create the order_subcomp object type.
    • Create the order_subcomp object type body.
    • Add a QUIT; statement.

    CREATE OR REPLACE
  TYPE order_subcomp UNDER order_comp
  ...
  INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE
  TYPE BODY order_comp IS
  ...
  END;
/
 
QUIT;

    You create an check_order.sql file calls:

    • An anonymous block PL/SQL program that calls an instance of the order_comp object type.
    • An anonymous block PL/SQL program that calls an instance of the order_subcomp object type.
    • Add a QUIT; statement.

    /* Set printing. */
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK OFF
 
/* Declare a test script. */
DECLARE
  lv_order  ORDER_COMP := order_comp('Clark Kent','Superman');
BEGIN
  dbms_output.put_line(lv_order.to_string);
END;
/
 
/* Declare a test script. */
DECLARE
  lv_order  ORDER_COMP := order_subcomp('Clark Kent','Superman','The Quest for Peace');
BEGIN
  dbms_output.put_line(lv_order.to_string);
END;
/
 
/* Quit the connection. */
QUIT;

    You create an order_compset.sh Bash script file calls:

    • You capture the SQL*Plus schema user’s name, password, and the target directory.
    • You assign the files you want to run to elements of the cmd variable in the following Bash shell script.

    #!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
directory="${3}"
 
echo "User name:" ${username}
echo "Password: " ${password}
echo "Directory:" ${directory}
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="order_comp.sql"
cmd[1]="order_subcomp.sql"
cmd[2]="check_order.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  sqlplus -s ${username}/${password} @${directory}/${i} > /dev/null
done

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.

The following articles discuss Bash processing:

There are five elements to this lab. It requires you to create:

  • A base_t object type and type body in a base_t.sql file written in an individual SQL script files, which includes a QUIT; statement.
  • A set of seven subtypes of the base_t object type written in individual SQL script files, which creates the respective object type, object type body, and includes a QUIT; statement.
  • A set of four subtypes of the elf_t object type written in individual SQL script files, which creates the respective object type, object type body, and includes a QUIT; statement.
  • A set of four SQL files; one to create a tolkien table, one to confirm valid object types, one to contain inserts into the tolkien table, and one to confirm the inserted object instances.
  • A Bash shell script that runs all the independent SQL script files.

It is important that you develop each piece sequentially. You should also test each piece of the code as you develop them.

Final Project Description

  1. [80 points] Create the 12 qualified object types:

    • The base_t object type and body without a dependency on another object type.
    • The dwarf_t, elf_t, goblin_t, hobbit_t, maia_t, man_t, and orc_t object types and bodies with a dependency on the base_t object type.
    • The noldor_t, silvan_t, sindar_t, and teleri_t object types and bodies with a dependency on the elf_t object type.

    and, position:

    • The DROP and CREATE SQL commands that create the tolkien table and tolkien_s sequence in the create_tolkien.sql file.
    • The object type validation and dependency diagnostic script in the type_validation.sql file.
    • The INSERT statements to populate 21 rows in the tolkien table in the insert_instances.sql file.
    • The diagnostic query that verifies the inserts in the query_instances.sql file.

    The sections cover the detailed tasks of the lab:

Final Project Details →

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

There are five elements to this lab. It requires you to create:

  • A base_t object type and type body in a base_t.sql file, which includes a QUIT; statement.

    base_t object type →

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

  • A set of seven subtypes of the base_t object type:

    • A dwarf_t object type and type body as a subtype of the base_t object type, and a QUIT; statement in a dwarf_t.sql file.

      dwarf_t object type →

       dwarf_t extends STUDENT.BASE_T
 dwarf_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 DWARF_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • An elf_t object type and type body as a subtype of the base_t object type, and a QUIT; statement in a elf_t.sql file.

      elf_t object type →

       elf_t extends STUDENT.BASE_T
 elf_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 ELF_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A goblin_t object type and type body as a subtype of the base_t object type, and a QUIT; statement in a goblin_t.sql file.

      goblin_t object type →

       goblin_t extends STUDENT.BASE_T
 goblin_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 GOBLIN_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A hobbit_t object type and type body as a subtype of the base_t object type, and a QUIT; statement in a hobbit_t.sql file.

      hobbit_t object type →

       hobbit_t extends STUDENT.BASE_T
 hobbit_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 HOBBIT_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A maia_t object type and type body as a subtype of the base_t object type, and a QUIT; statement in a maia_t.sql file.

      maia_t object type →

       maia_t extends STUDENT.BASE_T
 maia_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 MAIA_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A man_t object type and type body as a subtype of the base_t object type, and a QUIT; statement in a man_t.sql file.

      man_t object type →

       man_t extends STUDENT.BASE_T
 man_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 MAN_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • An orc_t object type and body as a subtype of the base_t object type, and a QUIT; statement in an orc_t.sql file.

      orc_t object type →

       orc_t extends STUDENT.BASE_T
 orc_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 
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 ORC_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

  • A set of four subtypes of the elf_t object type:

    • A noldor_t object type and type body as a subtype of the elf_t object type, and a QUIT; statement in a noldor_t.sql file.

      noldor_t object type →

       noldor_t extends STUDENT.ELF_T
 noldor_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 ELFKIND					    VARCHAR2(30)
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION NOLDOR_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_ELFKIND RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ELFKIND
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A silvan_t object type and type body as a subtype of the silvan_t object type, and a QUIT; statement in a silvan_t.sql file.

      silvan_t object type →

       silvan_t extends STUDENT.ELF_T
 silvan_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 ELFKIND					    VARCHAR2(30)
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION SILVAN_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_ELFKIND RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ELFKIND
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A sindar_t object type and type body as a subtype of the snidar_t object type, and a QUIT; statement in a sindar_t.sql file.

      sindar_t object type →

       sindar_t extends STUDENT.ELF_T
 sindar_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 ELFKIND					    VARCHAR2(30)
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION SINDAR_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_ELFKIND RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ELFKIND
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    • A teleri_t object type and type body as a subtype of the teleri_t object type, and a QUIT; statement in a teleri_t.sql file.

      teleri_t object type →

       teleri_t extends STUDENT.ELF_T
 teleri_t is NOT FINAL
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID						    NUMBER
 ONAME						    VARCHAR2(30)
 NAME						    VARCHAR2(30)
 GENUS						    VARCHAR2(30)
 ELFKIND					    VARCHAR2(30)
 
METHOD
------
 MEMBER FUNCTION GET_ONAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ONAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ONAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_NAME RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_NAME
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME				VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_GENUS RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_GENUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GENUS				VARCHAR2		IN
 
METHOD
------
 FINAL CONSTRUCTOR FUNCTION TELERI_T RETURNS SELF AS RESULT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION GET_ELFKIND RETURNS VARCHAR2
 
METHOD
------
 MEMBER PROCEDURE SET_ELFKIND
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ELFKIND			VARCHAR2		IN
 
METHOD
------
 MEMBER FUNCTION TO_STRING RETURNS VARCHAR2

    After you create the individual files for these object types, you should be able to list the files in the /home/student/cit325/final directory:

    ls *_t.sql

    It prints:

    base_t.sql   goblin_t.sql  man_t.sql     silvan_t.sql
dwarf_t.sql  hobbit_t.sql  noldor_t.sql  sindar_t.sql
elf_t.sql    maia_t.sql    orc_t.sql     teleri_t.sql

  • Create a set of four SQL files:
    • A create_tolkien.sql file that drops and creates the tolkien table and tolkien_s sequence.

      Detailed Instructions →

      /* Set environment variables. */
SET PAGESIZE 999
 
/* Write to log file. */
SPOOL create_tolkien.txt
 
/* Drop the tolkien table. */
DROP TABLE tolkien;
 
/* Create the tolkien table. */
CREATE TABLE tolkien
( tolkien_id NUMBER
, tolkien_character base_t);
 
/* Drop and create a tolkien_s sequence. */
DROP SEQUENCE tolkien_s;
CREATE SEQUENCE tolkien_s START WITH 1001;
 
/* Close log file. */
SPOOL OFF
 
/* Exit the connection. */
QUIT

      After you describe the table like this:

      DESCRIBE tolkien

       Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TOLKIEN_ID					    NUMBER
 TOLKIEN_CHARACTER				    BASE_T

    • A type_validation.sql file that validates whether you have created all the object types correctly.

      Detailed Instructions →

      /* Set environment variables. */
SET LINESIZE 72
SET PAGESIZE 999
 
/* Write to log file. */
SPOOL type_validation.txt
 
COL name FORMAT A16 HEADING "Base|Object Name"
COL referenced_name FORMAT A12 HEADING "Referenced|Object Name"
COL referenced_type FORMAT A12 HEADING "Referenced|Object Type"
COL TYPE            FORMAT A10 HEADING "Object|Type"
COL status          FORMAT A8  HEADING "Status"
SELECT   ud.name
,        ud.TYPE
,        CASE
           WHEN ud.referenced_name = 'STANDARD' THEN NULL
           ELSE ud.referenced_name
         END AS referenced_name
,        CASE
           WHEN ud.referenced_type = 'PACKAGE' THEN NULL
           ELSE ud.referenced_type
         END AS referenced_type
,        uo.status
FROM     user_dependencies ud INNER JOIN user_objects uo
ON       ud.name = uo.object_name
AND      ud.TYPE = uo.object_type
WHERE   (ud.name = 'BASE_T' AND ud.TYPE = 'TYPE' AND ud.referenced_name = 'STANDARD'
OR       ud.name LIKE '%_T' AND ud.TYPE = 'TYPE' AND NOT  referenced_name = 'STANDARD')
ORDER BY
  CASE
    WHEN name = 'BASE_T'   THEN 1
    WHEN name = 'DWARF_T'  THEN 2
    WHEN name = 'ELF_T'    THEN 3
    WHEN name = 'GOBLIN_T' THEN 4
    WHEN name = 'HOBBIT_T' THEN 5
    WHEN name = 'MAIA_T'   THEN 6
    WHEN name = 'MAN_T'    THEN 7
    WHEN name = 'ORC_T'    THEN 8
    WHEN name = 'NOLDOR_T' THEN 9
    WHEN name = 'SILVAN_T' THEN 10
    WHEN name = 'SINDAR_T' THEN 11
    WHEN name = 'TELERI_T' THEN 12
  END
, TYPE
, referenced_name DESC;
 
/* Close log file. */
SPOOL OFF
 
/* Close connection. */
QUIT

      It should return the following:

      Base		 Object     Referenced
Object Name	 Type	    Object Type  REFERENCED_TYPE    Status
---------------- ---------- ------------ ------------------ --------
BASE_T		 TYPE					    VALID
DWARF_T 	 TYPE	    BASE_T	 TYPE		    VALID
ELF_T		 TYPE	    BASE_T	 TYPE		    VALID
GOBLIN_T	 TYPE	    BASE_T	 TYPE		    VALID
HOBBIT_T	 TYPE	    BASE_T	 TYPE		    VALID
MAIA_T		 TYPE	    BASE_T	 TYPE		    VALID
MAN_T		 TYPE	    BASE_T	 TYPE		    VALID
ORC_T		 TYPE	    BASE_T	 TYPE		    VALID
NOLDOR_T	 TYPE	    ELF_T	 TYPE		    VALID
NOLDOR_T	 TYPE	    BASE_T	 TYPE		    VALID
SILVAN_T	 TYPE	    ELF_T	 TYPE		    VALID
SILVAN_T	 TYPE	    BASE_T	 TYPE		    VALID
SINDAR_T	 TYPE	    ELF_T	 TYPE		    VALID
SINDAR_T	 TYPE	    BASE_T	 TYPE		    VALID
TELERI_T	 TYPE	    ELF_T	 TYPE		    VALID
TELERI_T	 TYPE	    BASE_T	 TYPE		    VALID

    • An insert_instances.sql file that inserts 21 object type instances.

      Subtype Instances:
      Object Type OID Oname Name Genus Elfkind
      MAN_T 1 Man Boromir Men  
      MAN_T 2 Man Faramir Men  
      HOBBIT_T 3 Hobbit Bilbo Hobbits  
      HOBBIT_T 4 Hobbit Frodo Hobbits  
      HOBBIT_T 5 Hobbit Merry Hobbits  
      HOBBIT_T 6 Hobbit Pippin Hobbits  
      HOBBIT_T 7 Hobbit Samwise Hobbits  
      DWARF_T 8 Dwarf Gimli Dwarves  
      NOLDOR_T 9 Elf Feanor Elves Noldor
      SILVAN_T 10 Elf Tauriel Elves Silvan
      TELERI_T 11 Elf Earwen Elves Teleri
      TELERI_T 12 Elf Celeborn Elves Teleri
      SINDAR_T 13 Elf Thranduil Elves Sindar
      SINDAR_T 14 Elf Legolas Elves Sindar
      ORC_T 15 Orc Azog the Defiler Orcs  
      ORC_T 16 Orc Bolg Orcs  
      MAIA_T 17 Maia Gandalf the Grey Maiar  
      MAIA_T 18 Maia Radagast the Brown Maiar  
      MAIA_T 19 Maia Saruman the White Maiar  
      GOBLIN_T 20 Goblin The Great Goblin Goblins  
      MAN_T 21 Man Aragorn Men  

    • A query_instances.sql file that verifies the inserts of 21 valid instance inserts in the tolkien table.

      Detailed Instructions →

      /* Set environment variables. */
SET PAGESIZE 999
 
/* Write to log file. */
SPOOL query_instances.txt
 
/* Format and query results. */
COLUMN objectid         FORMAT 9999 HEADING "Object ID"
COLUMN name             FORMAT A20  HEADING "Name"
COLUMN description      FORMAT A40  HEADING "Description"
SELECT   t.tolkien_id AS objectid
,        TREAT(t.tolkien_character AS base_t).get_name() AS name
,        TREAT(t.tolkien_character AS base_t).to_string() AS description
FROM     tolkien t
ORDER BY 1, TREAT(t.tolkien_character AS base_t).get_name();
 
/* Close log file. */
SPOOL OFF
 
/* Close connection. */
QUIT

      It should return the following:

      Object ID Name                 Description                                      
--------- -------------------- ----------------------------------------         
     1085 Boromir              [1][Boromir][Men]                                
     1086 Faramir              [2][Faramir][Men]                                
     1087 Bilbo                [3][Bilbo][Hobbits]                              
     1088 Frodo                [4][Frodo][Hobbits]                              
     1089 Merry                [5][Merry][Hobbits]                              
     1090 Pippin               [6][Pippin][Hobbits]                             
     1091 Samwise              [7][Samwise][Hobbits]                            
     1092 Gimli                [8][Gimli][Dwarves]                              
     1093 Feanor               [9][Feanor][Elves][Noldor]                       
     1094 Tauriel              [10][Tauriel][Elves][Silvan]                     
     1095 Earwen               [11][Earwen][Elves][Teleri]                      
     1096 Celeborn             [12][Celeborn][Elves][Teleri]                    
     1097 Thranduil            [13][Thranduil][Elves][Sindar]                   
     1098 Legolas              [14][Legolas][Elves][Sindar]                     
     1099 Azoth the Defiler    [15][Azoth the Defiler][Orcs]                    
     1100 Bolg                 [16][Bolg][Orcs]                                 
     1101 Gandalf the Grey     [17][Gandalf the Grey][Maia]                     
     1102 Radagast the Brown   [18][Radagast the Brown][Maia]                   
     1103 Saruman the White    [19][Saruman the White][Maia]                    
     1104 The Great Goblin     [20][The Great Goblin][Goblins]                  
     1105 Aragorn              [21][Aragorn][Men]

  1. [20 points] Automate the process by adding all of your files as elements in the cmd array of the Bash shell script.

    You automate the procedure by creating the create_final.sh script, configure.

    Instruction Details →

    • You should test all of your scripts individually by calling them from the Linux command-line with the following syntax: 

      sqlplus -s student/student @script_name.sql

    • You use the following Bash shell template to create your template. You add the list of files to the cmd array in this Bash shell script. 

      #!/usr/bin/bash
 
# Assign user and password
username="student"
password="student"
directory="/home/student/Data/cit325/final"
 
echo "User name:" ${username}
echo "Password: " ${password}
echo "Directory:" ${directory}
 
# Define arrays.
declare -a cmd
declare -a log
 
# Assign elements to a command array.
cmd[0]="tools/object_cleanup.sql"
cmd[1]="base_t.sql"
cmd[2]="dwarf_t.sql"
cmd[3]="elf_t.sql"
cmd[4]="goblin_t.sql"
cmd[5]="hobbit_t.sql"
cmd[6]="maia_t.sql"
cmd[7]="man_t.sql"
cmd[8]="orc_t.sql"
cmd[9]="noldor_t.sql"
cmd[10]="silvan_t.sql"
cmd[11]="sindar_t.sql"
cmd[12]="teleri_t.sql"
cmd[13]="create_tolkien.sql"
cmd[14]="type_validation.sql"
cmd[15]="insert_instances.sql"
cmd[16]="query_instances.sql"
 
# Assign elements to a log array.
log[0]="object_cleanup.txt"
log[1]="base_t.txt"
log[2]="create_tolkien.txt"
log[3]="type_validation.txt"
log[4]="insert_instances.txt"
log[5]="query_instances.txt"
 
# Call the command array elements.
for i in ${cmd[*]}; do
  # Print the file to show progress and identify fail point.
  if ! [[ -f ${i} ]]; then
    echo "File [${i}] is missing."
  else
    sqlplus -s ${username}/${password} @${directory}/${i} > /dev/null
  fi
done
 
# Display the log array elements to console.
for i in ${log[*]}; do
  if [[ -f ${i} ]]; then
    cat ${i}
  else
    echo "File [${i}]."
  fi
done

    • You call the create_final.sh script with the following syntax: 

      create_final.sh

Written by michaelmclaughlin

July 3rd, 2017 at 6:13 pm

Posted in