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
REFstores 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 IScan constrain aREFto one object table.- A missing target can leave a dangling
REF.
See Oracle’s Data Types and Object-Relational Developer’s Guide.