Understanding related object tables (the REF operator)
Objective
Describe the SQL enhancements that allow you to query related object tables.
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.
CREATE OR REPLACE TYPE PETSTORE.CUSTOMER_TYPE
AS OBJECT
(
CUST_ID NUMBER(10),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
FULL_ADDRESS ADDRESS_TYPE,
PHONE_LIST PHONE_ARRAY,
LAST_UPDATE_DATE DATE,
UPDATED_BY_USER VARCHAR2(15)
);
Notes:
ADDRESS_TYPE and PHONE_ARRAY are likely previously defined object types or collections.
This object type is used to encapsulate customer-related data in a structured way, suitable for use in tables, PL/SQL procedures, or object-relational features in Oracle.
1) First, we create an object CUSTOMER_TYPE, which has information about a customer,
including the customer's name, address, and phone numbers.
The CUST_REF column is a REF to an object of type PETSTORE.CUSTOMER_TYPE.
The DETAIL_NEST is a nested table of type DETAIL_TABLE, and its storage table is NESTED_TAB_DETAIL.
This structure allows object-relational features such as references and nested tables in Oracle.
2) Now that we have the CUSTOMER_TYPE object, we will reference it with SALE_HEADER table by using the REF pointer.
What to pay attention to when Using REF
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:
PL/SQL-specific types like BOOLEAN or PLS_INTEGER.
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.
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`.
Using Cursor Variables (REF CURSORs)
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
package variables, and
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:
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:
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.