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 danglingREF. 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:
SELECT column_name_1, <column_name_2>
FROM table_name alias
WHERE object_name is DANGLING;
The SELECT clause for selecting attributes from the object table
The FROM clause for specifying the name of the object table
The IS DANGLING clause
Syntax for Dangling Reference in Oracle PL/SQL
There is no specific syntax for a dangling reference in Oracle PL/SQL. A dangling reference is an error condition that occurs when a program attempts to access or manipulate an object that no longer exists or has been removed. For example, the following PL/SQL code will cause a dangling reference error:
declare
emp_ref ref dept;
begin
select dept_id into emp_ref from dept where dept_name = 'Sales';
-- Delete the department
delete from dept where dept_name = 'Sales';
-- Attempt to access the department reference
emp_name := emp_ref.dept_name;
end;
In this example, the `emp_ref` variable is assigned a reference to the `Sales` department. However, the `Sales` department is then deleted. When the program attempts to access the `emp_ref` variable, it will cause a dangling reference error, because the `Sales` department no longer exists. There are a few things you can do to avoid dangling references in your PL/SQL code:
Be careful when deleting objects that are referenced by other objects.
Use the `IS DANGLING` predicate to check if an object reference is dangling before accessing it.
Use transaction control to ensure that objects are not deleted until all references to them have been removed.
If you do get a dangling reference error in your PL/SQL code, you can use the following steps to debug the problem:
Check the error message to see which object is causing the dangling reference.
Determine why the object was deleted or removed.
If the object was deleted accidentally, you may be able to restore it from a backup.
If the object was deleted intentionally, you will need to update your program to remove the references to the object.
By following these tips, you can avoid dangling references in your PL/SQL code and write more reliable and efficient programs.
Dangling Reference Syntax in Oracle
SELECT column_name_1, <column_name_2>
FROM table_name alias
WHERE object_name IS DANGLING;
Location 1
The SELECT clause for selecting attributes from the object table
Location 2
The FROM clause for specifying the name of the object table
Location 3
The IS DANGLING clause
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:
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.
