Detect and Repair Dangling REFs in Oracle 26ai

A dangling REF is an object reference whose target is unavailable. A common cause is deletion of a referenced row object while a referencing row remains. For example, an employee row can still contain a department reference after the department object has been deleted. The employee has not disappeared; the stored relationship no longer resolves to an available department.

Oracle AI Database 26ai provides the SQL predicate IS DANGLING to identify that condition. This lesson distinguishes dangling references from null references, demonstrates their detection, and explains repair choices. It also corrects a common misconception: Oracle supports referential integrity constraints on REF columns, so dangling references caused by missing targets are not something you must always prevent with custom triggers.

The examples concern typed references to row objects. A closed cursor variable, an out-of-scope local variable, or a dangling pointer in another programming language is a different problem. Diagnose the relationship represented by the actual REF column instead of treating every unavailable runtime resource as an Oracle dangling object reference.

Create a controlled training example

This script is illustrative and has not been executed against an Oracle database during the lesson rewrite. Use a fresh training schema, with the necessary privileges and unused names. The prefix dr_ separates this demonstration from the preceding lesson's objects. Do not replace an application's existing department types or tables to reproduce the exercise.

CREATE TYPE dr_department_t AS OBJECT (
  deptno NUMBER,
  dname  VARCHAR2(100)
);
/

CREATE TABLE dr_departments OF dr_department_t (
  deptno PRIMARY KEY
);

CREATE TABLE dr_employee_refs (
  empno    NUMBER PRIMARY KEY,
  ename    VARCHAR2(100),
  dept_ref REF dr_department_t SCOPE IS dr_departments
);

The first statement defines the department's structure. The object table stores referenceable department row objects. The employee table is relational, with a REF column restricted to that department table. SCOPE identifies the permitted target table; it does not enforce continued existence of the referenced row.

Oracle DDL has transaction implications, including implicit commits. Complete the schema creation before starting the reversible DML demonstration. The slash submits the type definition in SQL*Plus or SQLcl script execution; use your client's equivalent script conventions. A later ROLLBACK does not remove these created schema objects.

INSERT INTO dr_departments
VALUES (dr_department_t(10, 'ACCOUNTING'));
INSERT INTO dr_departments
VALUES (dr_department_t(20, 'RESEARCH'));

INSERT INTO dr_employee_refs
SELECT 1001, 'SMITH', REF(d)
FROM dr_departments d WHERE d.deptno = 10;

INSERT INTO dr_employee_refs
SELECT 1002, 'JONES', REF(d)
FROM dr_departments d WHERE d.deptno = 20;

INSERT INTO dr_employee_refs
VALUES (1003, 'LEE', NULL);

SAVEPOINT before_department_delete;

The primary-key predicates identify at most one department. Each insert-select adds its employee only if the required department exists. The third employee deliberately has no department reference. At this stage, the logical expected states are two available targets and one null reference. These are expected results from the sample data, not a captured database execution log.

Delete the target without deleting the referencing row

DELETE FROM dr_departments WHERE deptno = 10;

The training table uses a scoped REF without a referential integrity constraint. Deleting department 10 therefore leaves employee 1001's reference pointing to a target that no longer exists. Employee 1002 still points to department 20, and employee 1003 still has a null reference. The deletion changes one association's validity rather than deleting every employee.

Run the diagnostic queries before any commit or additional DDL. The same session can examine its uncommitted changes. Keeping this sequence within one transaction makes it possible to undo the demonstration without introducing permanent broken associations. In a production system, deletion permissions and business rules need explicit design; the permissive lab definition is intentional.

Detect the dangling reference with SQL

SELECT e.empno, e.ename
FROM dr_employee_refs e
WHERE e.dept_ref IS DANGLING;

For this example, the diagnostic would identify employee 1001, SMITH. It would not identify employee 1003 merely because that employee has no assignment. A null REF and a dangling REF are different states. The predicate detects a condition; it neither repairs the reference nor deletes the row.

Classify every row before deciding on repair

SELECT e.empno, e.ename,
       CASE
         WHEN e.dept_ref IS NULL THEN 'UNASSIGNED'
         WHEN e.dept_ref IS DANGLING THEN 'DANGLING'
         ELSE 'TARGET AVAILABLE'
       END AS reference_status
FROM dr_employee_refs e
ORDER BY e.empno;

After the sample deletion, the expected classifications are DANGLING for 1001, TARGET AVAILABLE for 1002, and UNASSIGNED for 1003. An investigation report should preserve the employee's identifier and relevant business context. A count alone is useful for monitoring but insufficient to choose a replacement department.

The order of the CASE tests documents the intended distinction. First detect an absent reference, then test a stored reference's target. Do not label a sale or employee row itself as dangling when it is the REF attribute that has that condition. Accurate terminology makes repair reports and application error messages easier to interpret.

Understand SQL dereferencing behavior

SELECT e.empno, DEREF(e.dept_ref) AS department_object
FROM dr_employee_refs e
ORDER BY e.empno;

In SQL, dereferencing a dangling REF returns a null object. The old lesson's claims that this necessarily raises ORA-22910 or a nonexistent-LOB error were incorrect. Do not use unrelated error codes as a validity test or claim that a null REF automatically terminates the session.

A program that receives a null object still needs to handle it appropriately before using attributes or invoking methods. Database APIs and procedural operations have their own error behavior. The SQL result described here should not be generalized into a promise that every attempted operation on an unavailable object succeeds.

When a query should return only usable relationships, state both requirements:

SELECT e.empno, e.ename, e.dept_ref.dname AS department_name
FROM dr_employee_refs e
WHERE e.dept_ref IS NOT NULL
  AND e.dept_ref IS NOT DANGLING;

This filter intentionally excludes unassigned and broken associations. It is appropriate for a report requiring available departments, but it would hide the problem rows in a repair report. Select the row-set policy before choosing the attribute projection.

Choose a repair that reflects the business rule

A diagnostic finding does not establish the correct replacement. A department might have been deleted accidentally, employees might need reassignment, or an optional relationship might legitimately be removed. Review the cause and the affected rows before changing data. Automatically assigning every problem row to a dummy customer or department can conceal errors and misrepresent business history.

UPDATE dr_employee_refs e
SET dept_ref = (
  SELECT REF(d)
  FROM dr_departments d
  WHERE d.deptno = 20
)
WHERE e.empno = 1001
  AND e.dept_ref IS DANGLING;

UPDATE dr_employee_refs e
SET dept_ref = NULL
WHERE e.empno = 1001
  AND e.dept_ref IS DANGLING;

Prevent missing targets with a REF constraint

For a separate prevention example, finish the rollback above first, then define another employee table:

CREATE TABLE dr_employee_refs_checked (
  empno    NUMBER PRIMARY KEY,
  ename    VARCHAR2(100),
  dept_ref REF dr_department_t REFERENCES dr_departments
);

REFERENCES on this REF column defines referential integrity against the department row objects. This is different from the earlier SCOPE-only declaration. With the demonstrated default constraint settings, deleting a department referenced by this checked table is rejected rather than leaving the child reference dangling. The table must actually contain referencing rows for that deletion check to matter.

The declaration still permits a null REF. Add an appropriate NOT NULL rule when every employee must have a department. Scope, target existence and mandatory assignment are related but distinct requirements. Existing bad data must be reviewed before introducing a constraint that validates previously stored associations.

A custom BEFORE DELETE trigger^[1] may support additional business policy, but it is not the default replacement for declarative integrity. A trigger on the child that prevents deleting the child does not solve deletion of the parent target. Custom cross-table checks require concurrency review rather than a simple assumption that an existence query guarantees integrity.

Use SQL detection inside PL/SQL

SET SERVEROUTPUT ON
DECLARE
  l_dangling_count PLS_INTEGER;
BEGIN
  SELECT COUNT(*) INTO l_dangling_count
  FROM dr_employee_refs e
  WHERE e.dept_ref IS DANGLING;

  DBMS_OUTPUT.PUT_LINE('Dangling references: ' || l_dangling_count);
END;
/

The SQL statement evaluates IS DANGLING, and PL/SQL receives the count. Run it at the stage whose state you want to measure: after the deletion it would count one affected reference; after the full lab rollback the sample rows are gone. The location in the transaction sequence determines the expected answer.

The block avoids presenting direct object-attribute access on a procedural REF variable as ordinary record access. It also avoids turning the SQL predicate into an unsupported standalone procedural IF example. A count can feed application logic or monitoring, but any actual repair needs a reviewed row selection and a defined transaction boundary.

The remaining referencing row can still contain valuable data even when one association is broken. Review that data before considering deletion. An employee record, a sale total, or a transaction timestamp might need to remain available for investigation and historical reporting. Removing a relationship and removing the entire business record are different decisions.

Consider an investigation in which the count changes between two checks. Another session might have reassigned an employee, deleted a department, or committed a previously pending change. Compare the session, transaction stage and selected identifiers before treating different counts as a faulty predicate. A diagnostic query reports the rows visible to that statement; it is not an automatic lock on every relationship that another transaction could change.

Similarly, a zero-row UPDATE is not proof that a repair succeeded. The employee might be absent, the reference might already be repaired, or the predicate might identify the wrong row. Inspect the current classification and the intended target rather than broadening the update indiscriminately. The narrow employee predicate in the sample is part of its documented repair decision, not an inconvenience to remove when no row matches.

Keep the detection evidence separate from the repair evidence. The former records what was found, and the latter records what action was selected and what changed. This separation makes it possible to explain a cleanup without claiming that every originally broken association should receive the same replacement.

For this lesson's objective, the essential tool is IS DANGLING on the REF column. Combine it with null checks to classify associations accurately, treat SQL DEREF's null result deliberately, and use declarative REF integrity when target existence must be enforced. These distinctions provide a sound foundation for the next lesson's dereferencing queries.