RelationalDBDesignRelationalDBDesign


PL/SQL   «Prev  Next»

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.