Lesson 3	Analyzing dangling references
Objective	Detect dangling references.

Analyzing Oracle Dangling References

Oracle allows you to delete an object that is the target of the REF without deleting the reference to it. For example, if an object is referenced within an object table, Oracle allows you to delete a record from the object, which is referenced within the object table. Oracle qualifies this state with a name: a dangling REF. This is roughly equivalent to what would happen if you deleted a department record without changing the records of the employees within the department.
For example, the SALE_HEADER object references the CUSTOMER_TYPE object. If a sale has been made to Anne Nicole and the record within CUSTOMER_TYPE for Anne Nicole is deleted, the sale record associated with that name is referred to as dangling. There is no declarative way to prevent dangling REFs, but Oracle allows you to identify such records by using the

IS DANGLING

clause within SQL. The following diagram explains the variables in the syntax:

Correct Usage Example (for Oracle 23c)

Here is a working example that checks for dangling references:

-- Assume you have a REF column:
CREATE TYPE person_t AS OBJECT (
  id NUMBER,
  name VARCHAR2(100)
);

CREATE TABLE person_tab OF person_t;

CREATE TABLE ref_holder (
  person_ref REF person_t SCOPE IS person_tab
);

-- Query to find dangling references:
SELECT *
FROM ref_holder
WHERE person_ref IS DANGLING;

Syntax for Dangling Reference in Oracle PL/SQL

In Oracle PL/SQL, a dangling reference is not a syntax but rather an error condition that occurs at runtime, not at compile time.
🔍 Definition: A "dangling reference" occurs when your PL/SQL program holds a reference (REF) to an object that has been deleted from the object table. This causes an error if you try to dereference or access the deleted object through that REF.
✅ Syntax Context: Oracle supports object references using the `REF` keyword:

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

CREATE TABLE person_tab OF person_t;

-- Table that holds REFs to person_t
CREATE TABLE ref_holder (
  ref_to_person REF person_t SCOPE IS person_tab
);

Now suppose:

DECLARE
  p_ref REF person_t;
BEGIN
  SELECT REF(p) INTO p_ref FROM person_tab p WHERE p.id = 1;

  DELETE FROM person_tab WHERE id = 1;

  -- Trying to access the deleted object now results in a dangling reference
  DBMS_OUTPUT.PUT_LINE(p_ref.name); -- ❌ Triggers a runtime error
END;

🛑 Error Example:
If you try to dereference a REF that points to a deleted object, Oracle will raise:

ORA-22910: cannot specify schema name for object type

or potentially:

ORA-22922: nonexistent LOB value

depending on how the object was accessed.
🧪 Detection:
You can test whether a reference is dangling using:

IF p_ref IS DANGLING THEN
   DBMS_OUTPUT.PUT_LINE('The reference is dangling.');
END IF;

✅ Summary:

No special syntax for a dangling reference—it's an error state.
Happens when a REF points to an object that was deleted.
Use IS DANGLING to test before dereferencing.

Oracle SQL Developer script that demonstrates 1. CREATE TYPE, 2. REF usage, 3. DEREF query, 4. IS DANGLING test

Here is a complete instructional Oracle SQL Developer script that demonstrates:
✅ Oracle SQL Developer Script: Object-Relational Example with `REF` and `DEREF`

-- 1. Create an object type for departments
CREATE OR REPLACE TYPE department_t AS OBJECT (
  deptno   NUMBER,
  dname    VARCHAR2(100)
);
/

-- 2. Create an object table to store department objects
CREATE TABLE department_tab OF department_t (
  CONSTRAINT dept_pk PRIMARY KEY (deptno)
);

-- 3. Insert sample departments
INSERT INTO department_tab VALUES (10, 'ACCOUNTING');
INSERT INTO department_tab VALUES (20, 'RESEARCH');
COMMIT;

-- 4. Create a table that holds REFs to department objects
CREATE TABLE employee_ref_tab (
  empno      NUMBER PRIMARY KEY,
  ename      VARCHAR2(100),
  dept_ref   REF department_t SCOPE IS department_tab
);

-- 5. Insert a reference to a department using a REF SELECT
DECLARE
  d_ref REF department_t;
BEGIN
  -- Get REF to department 10
  SELECT REF(d) INTO d_ref
  FROM department_tab d
  WHERE d.deptno = 10;

  -- Insert employee referencing department 10
  INSERT INTO employee_ref_tab VALUES (1001, 'SMITH', d_ref);
END;
/

COMMIT;

-- 6. Dereference the REF column to get the department name
SELECT e.empno, e.ename, DEREF(e.dept_ref).dname AS department_name
FROM employee_ref_tab e;

-- 7. Demonstrate a dangling reference
-- Delete the referenced department
DELETE FROM department_tab WHERE deptno = 10;
COMMIT;

-- Check if the REF is dangling
SELECT empno, ename
FROM employee_ref_tab
WHERE dept_ref IS DANGLING;

-- Optionally: reinsert department and reassign REF
DECLARE
  d_ref REF department_t;
BEGIN
  INSERT INTO department_tab VALUES (10, 'ACCOUNTING');
  
  SELECT REF(d) INTO d_ref
  FROM department_tab d
  WHERE d.deptno = 10;

  UPDATE employee_ref_tab
  SET dept_ref = d_ref
  WHERE empno = 1001;
END;
/

COMMIT;

-- Final: Show working DEREF after reassignment
SELECT e.empno, e.ename, DEREF(e.dept_ref).dname AS department_name
FROM employee_ref_tab e;

📘 Concepts Covered

Feature	Explanation
`CREATE TYPE`	Defines an object type, such as `department_t`
`REF`	Stores a reference (pointer) to a row in an object table, for example, `department_tab`
`DEREF`	Dereferences a `REF` to access the object's attribute values
`IS DANGLING`	Tests if a `REF` points to a deleted row, indicating a dangling reference
`SCOPE IS`	Restricts the `REF` to only reference a specific table, like `department_tab`, enhancing data integrity and safety

validation_clauses
{ VALIDATE REF UPDATE
[ SET DANGLING TO NULL ]
| VALIDATE STRUCTURE
[ CASCADE { FAST | COMPLETE } ]
[ into_clause ]
{ OFFLINE| ONLINE }
}

The following diagram uses an example to explain the variables in the syntax:

Dangling Reference Example — SELECT product_id, log_datetime FROM pet_care_log_obj_table WHERE product_id is DANGLING;

The SELECT clause

The FROM clause

The IS DANGLING clause

Concept of Dangling References in Oracle PL/SQL

In Oracle PL/SQL, a dangling reference occurs when a program attempts to access or manipulate an object that no longer exists or has been removed. This can happen in several ways, such as when a program tries to access a variable that has already been deallocated, or when a program tries to use a cursor that has been closed or invalidated. Dangling references can lead to unpredictable behavior and errors in PL/SQL programs. To avoid dangling references, it's important to properly manage the lifetime of objects and to ensure that all references to objects are properly cleaned up when they are no longer needed. One common technique for avoiding dangling references is to use exception handling to catch errors and ensure that objects are properly cleaned up before the program terminates. Another technique is to use RAISE_APPLICATION_ERROR to explicitly raise an error when a dangling reference is detected, allowing the program to gracefully handle the error and avoid any unexpected behavior.

Dangling reference example

SELECT product_id, log_datetime, FROM pet_care_log_obj_table WHERE product_id IS DANGLING

Location 1	The `SELECT` clause
Location 2	The `FROM` clause
Location 3	The `IS DANGLING` clause

IS DANGLING clause

The IS DANGLING clause helps identify a dangling reference and update it with a suitable reference. Let us look at the example of Anne Nicole. After the CUSTOMER_TYPE record for Anne Nicole has been deleted, the associated sale record is now dangling. To resolve this, you can create a dummy CUSTOMER_TYPE record and update all such records with a REF to this dummy record.

How can we avoid Dangling References?

To avoid dangling references, you can write a pre-delete trigger^[1] on the table that contains the parent object. For example, a predelete trigger can be defined on a child record that has a reference to its parent record. This trigger will check whether the parent record exists. If the parent record exists, the trigger will prevent the user from deleting the child record. You can also use a foreign key in combination with REF to avoid this issue. To do so, you will have to include an additional attribute within the child object and include a FOREIGN KEY clause within the CREATE TABLE statement for the parent table.
In the next lesson, we will write a query using the DEREF data type to retrieve related data. Now that you have learned about detecting dangling references, read the paragraph below to learn more about detecting dangling references.

Detecting Dangling References in Oracle

What one has to do is update the dangling references to an object within a table.
After you completed the required entry fields to connect to the database, you completed the following steps:

You began to build the UPDATE SQL statement.
Then you updated the dangling references to an object within a table.
After the process was completed, SQL*Plus displayed the result of the compilation of your query, and you clicked exit to end the simulation.

Use Of The Dangling Clause

The DANGLING clause is used to test for a dangling REF. In some situations if the application is not coded properly a REFed value could be deleted without removing the REF value in a dependent table. If this deletion of a master object instance without removal of the dependent REF value occurs, the REF value left in the dependent table is said to be DANGLING and an result in problems for

retrieves,
updates and
deletes.

An example of the use of this clause would be to set all dangling REF values to null via an update statement such as:

IS DANGLING

  
UPDATE emp SET edept=NULL 
WHERE edept IS DANGLING;

So unless you are absolutely sure that you have no dangling ref values you should use the DANGLING clause to ensure that the records you retrieve are complete. The clause can be used with both IS and IS NOT:

IS NOT DANGLING
```
 
SELECT ename, enumber, DEFRE(edept) 
FROM emp WHERE edept IS NOT DANGLING;
```
Always be sure that a REF value is not null before attempting to REF or DEREF it or your session may be terminated.

[1]Pre-delete trigger: A pre-delete trigger is a trigger which is fired before a record is deleted from a table.