Chapter 9. Object Technology

Oracle8 ushered us into the brave new world of object-oriented programming. . .almost. Since Oracle is, after all, a relational database, much of the object technology introduced in the Oracle8 (as well as the Oracle8i) release is a hybrid between true object data and relational data. The CREATE TYPE command, for example, looks suspiciously like a familiar record or table, with the exception that you can now include methods (procedures and functions) that act on the object’s attributes (columns and fields). This chapter tests your understanding of object types and your ability to apply object-oriented principles when designing a data structure.

Beginner

9-1.

True or false? An object type:

  1. Is a database construct created via the CREATE TYPE statement.

  2. Defines a data structure with attributes, each having a single datatype.

  3. Defines legal methods or operations on the object’s attributes.

  4. Is a template only and holds no actual data.

9-2.

True or false? An object type can serve as the datatype of:

  1. Each of the rows in a table.

  2. A column in a table.

  3. A PL/SQL variable.

  4. A PL/SQL module’s IN, OUT, or RETURN parameter.

  5. A index-by table, nested table, or VARRAY.

  6. A “field” in a PL/SQL record variable.

9-3.

A constructor is a special method that creates an instance of an object from an object type. True or false? A constructor:

  1. Has the same name as its object type.

  2. Is a function rather than a procedure.

  3. Returns an instance of its object type.

  4. Has one parameter per attribute of the object type.

  5. Is available “by default” for each object type.

  6. Has an analogous “destructor” method for deletion of an object.

9-4.

Which of the following statements correctly describes how to specify the source code for an object’s method(s)?

  1. In an object body that is separate from the object specification—much like a package specification and its separate package body:

    CREATE OR REPLACE TYPE BODY CD_t
AS
   MEMBER FUNCTION set_title  (title_in IN VARCHAR2)
   RETURN CD_t
   IS
      the_cd CD_t := SELF;
   BEGIN
      IF title_in IS NOT NULL
      THEN
         the_cd.title := title_in;
      END IF;
      RETURN the_cd;
   END set_title;
END;

  2. In a standalone procedure or function:

    CREATE OR REPLACE FUNCTION set_title (title_in IN VARCHAR2)
RETURN CD_t
AS MEMBER OF CD_t
IS
   the_cd CD_t := SELF;
BEGIN
   IF title_in IS NOT NULL
   THEN
      the_cd.title := title_in;
   END IF;
   RETURN the_cd;
END set_title;

9-5.

Which of the following statements about object methods are true, and which are false?

  1. Object methods can be declared as STATIC, indicating they do not operate on a particular object.

  2. STATIC object methods were introduced with the release of Oracle8i.

  3. Non-STATIC object methods operate within the context of a specific object instance.

  4. Non-STATIC object methods have an implied parameter called SELF.

9-6.

True or false? SELF can be included as a parameter in the parameter list of an object method.

9-7.

Suppose that we have an object table of compact discs:

CREATE TABLE compact_discs OF CD_t;

Which of the following examples retrieves an object row into an object variable?

  1. Use the OBJECT operator to SELECT from the object table:

    DECLARE
   the_cd   CD_t;

   CURSOR CD_cur IS
      SELECT OBJECT(cd)
        FROM compact_discs cd
       WHERE id = 100;
BEGIN
   OPEN  CD_cur;
   FETCH CD_cur INTO the_cd;
END;

  2. Use the VALUE operator to SELECT from the object table:

    DECLARE
   the_cd   CD_t;

   CURSOR CD_cur IS
      SELECT VALUE (cd)
        FROM compact_discs cd
       WHERE id = 100;

BEGIN
   OPEN  CD_cur;
   FETCH CD_cur INTO the_cd;
END;

9-8.

Each row in an object table is automatically assigned an Object Identifier (OID) by Oracle. True or false? The OID:

  1. Is a 16-byte RAW value

  2. Uniquely identifies an object in the database

  3. Resides in a “hidden” column in the object table

  4. Has a value that cannot be “seen” by the user

9-9.

Which of the following types of objects have an OID?

  1. Column objects

  2. Transient objects stored in PL/SQL variables

  3. Nested objects, for example, the previous nested table of lost CDs named lost_cd_tab

9-10.

A REF is a reference to a row object in an object table. REFs are the basis of relationships between objects and allow “object joins.” True or false?

  1. A REF points at a row object. It encapsulates the row object’s OID.

  2. REFs are used like a foreign key.

  3. The keyword REF can modify a datatype, indicating a pointer to an object.

  4. The REF operator accepts a table alias and returns a pointer to a row object that is constructed from the row object’s OID.

  5. A REF can point to a column object.

9-11.

Suppose that item 1 of CD order 10000 in the cd_order_items table points to the Beatles’ White Album CD object in the compact_discs table (see the solution for 9-10 for the associated code). What is the result produced by the following SQL statement? Can you delete a compact disc if there is an order pointing to it?

DELETE FROM compact_discs
WHERE artist = 'Beatles'
AND   title = 'White Album';

9-12.

Here is one way of joining a CD from the cd_order_items table to its definition in the compact_discs table:

SELECT items.item_id, cd.artist, cd.title
  FROM cd_order_items items, compact_discs cd
 WHERE items.cd_ref = REF (cd)
   AND items.order_id = 10000;

What is another way of expressing this join using object navigation?

9-13.

DEREF is the “de-reference” operator. Given a REF, it returns the referenced object. DEREF is the logical inverse of REF. It “un-does” the REF:

DECLARE
   the_cd CD_t;
BEGIN
   SELECT DEREF(cd_ref)
     INTO the_cd
     FROM cd_order_items
    WHERE order_id = 10000
      AND item_id = 1;

   DBMS_OUTPUT.PUT_LINE('Artist: ' || the_cd.artist);
END;

What is produced by the code segment?

9-14.

Suppose you have a table of music fans and their favorite CDs. Note that favorite_cd is a column object, not a REF:

SQL> DESC fan_favorites;

Name           Null?    Type
-------------- -------- -------------------
MUSIC_FAN_NAME          VARCHAR2(60)
FAVORITE_CD             CD_T

What happens when you try to ORDER BY an object?

SELECT music_fan_name,
       favorite_cd
  FROM fan_favorites
 ORDER BY favorite_cd;

9-15.

Oracle must be told how to compare objects of the same type. This allows objects to be ORDERed, for example.

The MAP and ORDER methods allow Oracle to compare objects of the same type. MAP translates an object into a scalar value Oracle can use for comparison. ORDER compares two objects. Either MAP or ORDER may be used for an object type, but not both.

You might specify a MAP method for our CD object type as follows:

CREATE TYPE CD_t AS OBJECT (
   id    INTEGER,
   title VARCHAR2(60),
   artist VARCHAR2(60),

      MEMBER FUNCTION set_title  (title_in IN VARCHAR2)
      RETURN CD_t,

   MAP MEMBER FUNCTION compare RETURN VARCHAR2
);

Give an example of the contents found in the object body for the MAP method “compare.”

Intermediate

9-16.

True or false?

  1. With the release of the Oracle Objects option, the USER_OBJECTS view became available in the data dictionary.

  2. An entry for each object type is found in the USER_OBJECTS view.

  3. An entry for each object instance is found in the USER_OBJECTS view.

9-17.

True or false? A constructor:

  1. Can be modified by a developer.

  2. Can be called in SQL or PL/SQL.

  3. Accepts attributes in named notation or positional notation.

9-18.

What is wrong with the following object type definition?

CREATE TYPE CD_t AS OBJECT (
   id            INTEGER,
   title         VARCHAR2(60) := 'Greatest Hits',
   artist        VARCHAR2(60),
   label         VARCHAR2(60),
   MEMBER FUNCTION
      set_title  (title_in    IN VARCHAR2)
         RETURN CD_t,
   MEMBER FUNCTION
      set_artist (artist_in   IN VARCHAR2)
         RETURN CD_t,
   MEMBER FUNCTION
      set_label (label_in     IN VARCHAR2)
         RETURN CD_t
);

9-19.

What is wrong with the following object type definition?

CREATE TYPE CD_t AS OBJECT (
   id                  INTEGER,
   title               VARCHAR2(60),
   artist              VARCHAR2(60),
   label               VARCHAR2(60),
   classification      VARCHAR2(60),

   MEMBER FUNCTION  set_title  (title_in    IN VARCHAR2)
      RETURN CD_t;
   MEMBER FUNCTION  set_artist (artist_in   IN VARCHAR2)
      RETURN CD_t;
);

9-20.

True or false? An object method:

  1. Can be implemented in PL/SQL.

  2. Can be implemented via an external procedure, such as a procedure written in the C language.

  3. Can be implemented in Java.

9-21.

What happens when you forget to FETCH something as an object, as shown next? How can you fix the problem?

DECLARE
   the_cd   CD_t;

   CURSOR   cd_cur
   IS
   SELECT *
     FROM compact_discs cd
    WHERE id = 100;

BEGIN
   OPEN cd_cur;
   FETCH cd_cur INTO the_cd;
   CLOSE cd_cur;
   DBMS_OUTPUT.PUT_LINE (the_cd.id || ' ' || the_cd.title);
END;

9-22.

True or false?

  1. OIDs are immutable : if you want to change the value of an OID, you must delete the object and recreate it.

  2. Oracle automatically creates a unique index on an object table’s hidden OID column.

  3. As with ROWIDs, OID values are lost and recreated anew when an object table is exported and then imported.

9-23.

What output is generated by the following code segment?

DECLARE
   new_cd CD_t;
BEGIN
   IF new_cd IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE ('New CD is NULL'),

      IF new_cd.title IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE ('New CD Title is NULL'),
      ELSE
         DBMS_OUTPUT.PUT_LINE ('New CD Title is NOT NULL'),
      END IF;

   ELSE
      DBMS_OUTPUT.PUT_LINE ('New CD is NOT NULL'),
   END IF;

END;

9-24.

What is wrong with the following code segment?

DECLARE
   new_cd   CD_t;
BEGIN
   new_cd.id := 75000;
   new_cd.artist := 'Grateful Dead';
   new_cd.title := 'Live in Branson';
END;

9-25.

Given a REF, the UTL_REF package allows the following operations on the referenced object:

  • Lock

  • Retrieve

  • Update

  • Delete

This is possible (without UTL_REF’s knowing the table name) where the referenced object resides. This allows programs to be “blind” to the table where an object is stored.

Write a code segment that locks and updates the title of the White Album CD object.

Expert

9-26.

Which of the following statements accurately describe an object view and its capabilities?

  1. An object view is any SQL view found in the ALL_OBJECTS data dictionary view.

  2. An object view is a view that presents data stored in relational tables as an object.

  3. You can use an object view to navigate using REFs rather than joins.

  4. Object views can help you circumvent “schema evolution” problems that arise when working with the object layer of the Oracle RDBMS.

  5. If you decide to use object views, you can no longer make direct references to relational tables in your application.

9-27.

Object views allow us to layer an object-like approach onto existing (non-object) relational tables. Let’s apply object views to our compact disc example. Suppose that our compact_discs table is no longer an object table, but instead a relational table:


/* Filename on web page: objview.sql */
CREATE TABLE compact_discs (
   ID      INTEGER NOT NULL,
   title   VARCHAR2(60),
   artist  VARCHAR2(60),
   label   VARCHAR2(60),
   PRIMARY KEY (ID)
);

INSERT INTO compact_discs VALUES
   (100, 'White Album', 'Beatles', 'Apple'),

We would then have a second table of keywords that are related to a compact_disc:

CREATE TABLE cd_keywords (
   cd_id        INTEGER NOT NULL REFERENCES compact_discs (id),
   keyword      VARCHAR2(60) NOT NULL,
   PRIMARY KEY  (cd_id, keyword)
);

INSERT INTO cd_keywords VALUES (100, 'Lennon'),
INSERT INTO cd_keywords VALUES (100, 'McCartney'),

What you want to do now is create a CD object (actually an object view) that has CD attributes and a collection of its related keywords.

Given these two definitions (collection and object type):

CREATE TYPE cd_keyword_tab_t AS TABLE OF VARCHAR2(60);

CREATE TYPE CD_t AS OBJECT (
   id         INTEGER,
   title      VARCHAR2(60),
   artist     VARCHAR2(60),
   label      VARCHAR2(60),
   keywords   cd_keyword_tab_t,
   MAP MEMBER FUNCTION compare RETURN VARCHAR2
);

What is the definition of an object view that returns objects of type CD_t?

9-28.

The following creation and drop of an object type succeeds:

CREATE TYPE temp_t AS OBJECT (
   name VARCHAR2(1000),
   hobbies VARCHAR2(1000));
/

DROP TYPE temp_t;

But after creating a table of objects based on this type, the next attempt to drop the type fails:

CREATE TYPE temp_t AS OBJECT (
   name VARCHAR2(1000),
   hobbies VARCHAR2(1000));
/

CREATE TABLE temp_tab OF temp_t;

DROP TYPE temp_t;

Why can’t you drop the object type as shown above, and how do you get around this problem?

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.222.89