DEREF returns the object instance that the REF value points to. This is more easily explained with a simple example.
In this case, DEREF is passed the REF value for the correlation ID of the inventory_tbl:
SELECT DEREF(REF(i))
FROM inventory_tbl i
WHERE item_id = 1;
The object instance associated with the REF value of item_id 1 is returned:
1) DEREF is an operator that dereferences a reference. DEREF accepts a correlation argument, which is the reference (REF) to an object, and returns the reference object.
DEREF is an operator that dereferences a reference.
DEREF returns the reference object.
DEREF accepts a correlation argument, which is the reference (REF) to an object
2) Syntax for using the DEREF operator is as follows
SELECT DEREF(object_reference)
FROM object_table_name
WHERE column_name=<variable_value>
When the REF argument is passed to the DEREF operator, it returns the object.
3) In this example, we use the DEREF operator to return the object from PET_CARE_LOG_OBJ_TABLE:
SELECT DEREF (product_id)
FROM pet_care_log_obj_table WHERE log_datetime>=TO_DATE('01/02/2000',
'MM/DD/YY');