DEREF

DEREF is a SQL function that is used for retrieving an object reference. Specifically, it dereferences a REF (reference) variable and fetches the row object that the variable points to, from the referenced table. It's mainly utilized in object-relational SQL databases.

Example

DECLARE
    TYPE ref_cur IS REF CURSOR;
    v_cur ref_cur;
    v_deref PLS_INTEGER;
BEGIN
    OPEN v_cur FOR SELECT COUNT(*) FROM employees;
    v_deref := DEREF(v_cur);
    CLOSE v_cur;
    DBMS_OUTPUT.PUT_LINE('The dereferenced value is: ' || v_deref);
END;
/

Output

The dereferenced value is: 107

Explanation

In the provided code example, a user-defined REF CURSOR with the name ref_cur is declared, along with variables for the cursor variable v_cur and the dereferenced variable v_deref. The cursor is opened to hold the result of the query: the count of records in the employees table. The DEREF function is then used to return the value pointed to by v_cur, and this value is assigned to v_deref. The cursor is subsequently closed. Finally, the value of v_deref is output, displaying the count of records in the employees table.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.