Understanding related Object Tables in Oracle

Within Oracle, references can be obtained for row objects only. For every row object that is stored within an object table, Oracle generates and associates an object identifier (OID) for it. However, objects that are stored within a column do not have such a unique identifier. In the following series of images, we create an object and reference it within another object by using the REF pointer:

• What is the purpose of the REF Operator in Oracle?
  The REF operator in Oracle is used to create a reference to an object or a row in a table. It returns a REF value that can be used to reference a specific row or object in a table or view. The purpose of the REF operator is to provide a way to navigate between related objects or rows in different tables, without requiring the use of complex join operations. This can simplify the process of accessing related data in a database and can make it easier to work with complex data structures. For example, if you have a table that contains a foreign key reference to another table, you can use the REF operator to create a reference to a specific row in the related table. You can then use this reference to access the related data without having to perform a join operation. Overall, the REF operator can be useful for managing complex data relationships and for improving the performance of database queries by reducing the need for complex join operations.

For nested tables and VARRAYs (variable-size arrays) declared within PL/SQL, the element type can be almost any PL/SQL data type except for `REF CURSOR`.
Key Points:

1. Allowed Element Types:
   • Scalar types (e.g., NUMBER, VARCHAR2, DATE, BOOLEAN).
   • Composite types (e.g., records, objects).
   • Other collections (nested tables or VARRAYs).
   • PL/SQL-specific types like BOOLEAN or PLS_INTEGER.
2. Restriction on REF CURSOR:
   • A REF CURSOR is a dynamic cursor type (a pointer to a result set), and it cannot be stored as an element in a collection. This is because collections are meant to hold data, not references to result sets.
3. Other Exceptions:
   • While REF CURSOR is explicitly excluded, there are a few other limitations in some Oracle versions (e.g., you cannot have a collection of collections directly; instead, you must use a collection of an object type that contains a collection).

Example:

DECLARE
   -- Valid: Nested table of numbers
   TYPE numbers_nt IS TABLE OF NUMBER;
   
   -- Valid: VARRAY of strings
   TYPE names_varray IS VARRAY(100) OF VARCHAR2(100);
   
   -- Valid: Nested table of record type
   TYPE emp_rec IS RECORD (id NUMBER, name VARCHAR2(100));
   TYPE emp_nt IS TABLE OF emp_rec;
   
   -- Invalid: Nested table of REF CURSOR (not allowed)
   -- TYPE refcur_nt IS TABLE OF SYS_REFCURSOR; -- This will cause an error
BEGIN
   NULL;
END;

Why `REF CURSOR` is Excluded:

• REF CURSOR is a runtime construct (a handle to a query result), not a storable data value.
• Collections are designed to store data, not live cursors or references to result sets.

Remember, the element type of a nested table or VARRAY in PL/SQL can be any PL/SQL data type except `REF CURSOR`.

Like a cursor, a cursor variable points to the current row in the result set of a multiple-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns. You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic. Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote subprogram calls

To query a table that is referenced to an object, we can use standard SQL along with the VALUE, REF, and DEREF operators. Let's examine these operators in detail.

• REF operator
  Object Unique Identifier will return a REF (reference) of the object, rather than the object itself. Like the VALUE operator, REF takes a correlation variable^[1] as an argument. In this context, a correlation variable is simply a table alias.

Typically, a REF value comprises the object's unique identifier (OID) associated with the object table and the row identifier (ROWID) of the row object. The ROWID is used as a means to provide quick access to the object. A REF can be used for implicit joins and easy navigation. REF may sound a lot like a foreign key. However, there are important differences. REF is more reliable and persistent than a foreign key. This is because REFs do not refer to user-changeable values. Rather, they refer to invisible, internal values such as OID and ROWID.

• Objects implemented through TYPEs
  Oracle implements Objects through the use of TYPEs, defined in a similar way to packages. Unlike packages in which the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes and properties, defined in the same way as
  1. package variables, and
  2. member functions or procedures.
  If a type contains member functions or procedures, the procedural work for these elements is defined in the TYPE BODY.
  To see how objects can be used, suppose an object is created to represent a person. In this case, a person is defined by three attributes: first_name, last_name and date_of_birth. Returning the age of the person is also desired, so this is included as a member function, get_age.
  

  CREATE OR REPLACE TYPE t_person AS OBJECT (
     first_name     VARCHAR2(30),
     last_name      VARCHAR2(30),
     date_of_birth  DATE,
     MEMBER FUNCTION get_age RETURN NUMBER
   );
   /
  

  
  Type created.

The following example illustrates this:

SELECT REF(pc)
FROM pet_care_log_obj_table pc
WHERE to_char(pc.last_update_datetime, 'DD-MMM-YY') = 
 '12-DEC-99';

Executing this SQL returns the following result set:

REF(PC)
------------------------------------------------------
0000280209DEE8F40AC4B911D39B07000000000000DEE8F3E3C4B9
 11D39B07000000000000008003370002

Given a reference, DEREF will return the original object. We will learn more about DEREF later in this module. The following example illustrates this: The link below contains additional information about Database Design.

SELECT DEREF(pc.product_id)
FROM pet_care_log_obj_table pc
WHERE to_char(pc.last_update_datetime, 'DD-MMM-YY') =
 '12-DEC-99';

Executing this SQL returns the following result set:

DEREF(PC.PRODUCT_ID)(PRODUCT_ID, PRODUCT_NAME, 
 PACKAGE_ID, CURRENT_INVENTORY_COUNT, STORE_COST, 
 SALE
---------------------------------------------------
PRODUCT_TYPE(11, 'Fish', NULL, 150, 0, .99, 
 '23-OCT-99', 'JANET', 'Y')

The VALUE operator returns an object, rather than a list of attributes. It takes a correlation variable as an argument. In this context, a correlation variable is simply a table alias. The following example illustrates how the VALUE operator is used:

SELECT VALUE(pc)
FROM pet_care_log_obj_table pc
WHERE to_char(pc.last_update_datetime, 'DD-MMM-YY') = '12-DEC-99';

The result set of this query is a set of objects, not a set of attributes, as shown below:

VALUE(PC)(PRODUCT_ID, LOG_DATETIME, CREATED_BY_USER,
 LOG_TEXT, LAST_UPDATE_DATETIME)
----------------------------------------------------
PET_CARE_LOG_TYPE(0000220208DEE8F3F3C4B911D39B07000
 000000000DEE8F3E2C4B911D39B07000000000000, '12-DE
C-99', 'MARK', 'Fish that are sold must be written 
 down on the FISH LOG sheet for proper funds if 
 needed.', '12-DEC-99')

[1] Correlation variable: A correlation variable is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation variable.