Menu

Oracle REF Data Type: Object References, SCOPE, and DEREF

Oracle REF is an object-relational data type that stores a logical reference to a row object.

A REF is not the same thing as an ordinary foreign key. It contains a reference to an object instance identified by an object identifier (OID).

Basic Model

First define an object type:

CREATE TYPE person_t AS OBJECT (
  id   NUMBER,
  name VARCHAR2(100)
);
/

Then create an object table:

CREATE TABLE person_obj_table OF person_t;

A REF person_t value can point to a row object stored in that object table.

Creating a REF Column

CREATE TABLE contacts (
  person_ref REF person_t,
  contact_date DATE
);

The person_ref column can hold a reference to an object of type person_t.

Getting a REF with REF()

Insert a row object:

INSERT INTO person_obj_table
VALUES (person_t(1, 'Ada'));

Then obtain its reference with the REF() function:

SELECT REF(p)
FROM person_obj_table p
WHERE p.id = 1;

You can store that value in a REF column:

INSERT INTO contacts (person_ref, contact_date)
SELECT REF(p), DATE '2026-10-07'
FROM person_obj_table p
WHERE p.id = 1;

Reading the Referenced Object with DEREF()

DEREF() returns the object instance referenced by a REF:

SELECT DEREF(c.person_ref)
FROM contacts c;

You can also navigate attributes in SQL:

SELECT c.person_ref.name
FROM contacts c;

Scoped REFs

A REF column can be constrained to a specific object table with SCOPE IS:

CREATE TABLE contacts (
  person_ref REF person_t SCOPE IS person_obj_table,
  contact_date DATE
);

A scoped REF takes less storage and gives Oracle more information for navigating the reference efficiently.

REF Constraints and Foreign Keys

A REF models relationships between objects. It is not a drop-in replacement for a conventional relational foreign key.

Oracle can apply a referential constraint to a REF column, but the referenced value must still be a row object of the corresponding object type.

For most purely relational schemas, normal primary-key and foreign-key columns remain simpler and more portable.

Dangling REFs

If a REF points to an object that no longer exists, it is called a dangling REF.

SELECT *
FROM contacts c
WHERE c.person_ref IS DANGLING;

A dangling REF is different from a NULL REF.

When to Use REF

REF is mainly useful when you intentionally use Oracle’s object-relational features:

  • object types
  • object tables
  • object views
  • navigation between related object instances

For ordinary application tables, a traditional foreign key is generally easier to understand and maintain.

Summary

  • REF stores a logical reference to a row object.
  • The referenced object belongs to an Oracle object type.
  • Use REF(alias) to obtain a reference.
  • Use DEREF() to retrieve the referenced object.
  • SCOPE IS can constrain a REF to one object table.
  • A missing target can leave a dangling REF.

See Oracle’s Data Types and Object-Relational Developer’s Guide.