PL/SQL   «Prev  Next»

Lesson 3Analyzing dangling references
ObjectiveDetect 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: 

SELECT column_name_1, <column_name_2>
FROM table_name alias
WHERE object_name is DANGLING;
  1. The SELECT clause for selecting attributes from the object table
  2. The FROM clause for specifying the name of the object table
  3. The IS DANGLING clause
Dangling Reference Syntax
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;
  1. The SELECT clause
  2. The FROM clause
  3. The IS DANGLING clause
Dangling Reference Example

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 pre-delete triggers 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.
Pre-delete trigger: A pre-delete trigger is a trigger which is fired before a record is deleted from a 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, click the link below to learn more about dangling references.
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:
  1. You began to build the UPDATE SQL statement.
  2. Then you updated the dangling references to an object within a table.
  3. 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
  1. retrieves,
  2. updates and
  3. 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.