Home » SQL & PL/SQL » SQL & PL/SQL » About REF datatype (10.2 to 19)
About REF datatype [message #684783] Fri, 20 August 2021 11:43 Go to next message
Michel Cadot
Messages: 68010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Background
We have an application which uses an object table of a type containing an attribute of REF datatype.
This application first gets the REF data into a variable, makes some work and then wants to retrieve the data the REF is pointing to.
The problem is that there is no REF type for a variable so the applications gets it into a string variable and then fails to make it back a REF variable at Oracle side.

Test case to reproduce it with SQL*Plus
CREATE TYPE emp_person_typ AS OBJECT (
  name     VARCHAR2(30),
  manager  REF emp_person_typ 
)
/
CREATE TABLE emp_person_obj_table OF emp_person_typ
/
INSERT INTO emp_person_obj_table VALUES (emp_person_typ ('John Smith', NULL))
/
INSERT INTO emp_person_obj_table
  SELECT emp_person_typ ('Bob Jones', REF(e))
    FROM emp_person_obj_table e
    WHERE e.name = 'John Smith'
/
COMMIT
/

Execution
SQL> CREATE OR REPLACE TYPE emp_person_typ AS OBJECT (
  2    name     VARCHAR2(30),
  3    manager  REF emp_person_typ
  4  )
  5  /

Type created.

SQL> CREATE TABLE emp_person_obj_table OF emp_person_typ
  2  /

Table created.

SQL> INSERT INTO emp_person_obj_table VALUES (emp_person_typ ('John Smith', NULL))
  2  /

1 row created.

SQL> INSERT INTO emp_person_obj_table
  2    SELECT emp_person_typ ('Bob Jones', REF(e))
  3      FROM emp_person_obj_table e
  4      WHERE e.name = 'John Smith'
  5  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.
Now get the manager of "Bob Jones", the REF value and the data pointed by it:
SQL> SELECT manager FROM emp_person_obj_table WHERE name = 'Bob Jones'
  2  /
MANAGER
-------------------------------------------------------------------------------
0000220208AE3DFEB265584EA78F3F8BD8DF20B8B64BB5EAD741284600AC02C0466F54D0D5

1 row selected.

SQL> SELECT DEREF(manager) FROM emp_person_obj_table WHERE name = 'Bob Jones'
  2  /
DEREF(MANAGER)(NAME, MANAGER)
-------------------------------------------------------------------------------
EMP_PERSON_TYP('John Smith', NULL)

1 row selected.

Mimick the application
First step: retrieve the REF value into a variable:
SQL> VARIABLE ref VARCHAR2(200)
SQL> EXECUTE SELECT RAWTOHEX(manager) INTO :ref FROM emp_person_obj_table WHERE name = 'Bob Jones'

PL/SQL procedure successfully completed.

SQL> PRINT ref
REF
--------------------------------------------------------------------------------------------------
00220208AE3DFEB265584EA78F3F8BD8DF20B8B64BB5EAD741284600AC02C0466F54D0D5
Now the application makes some work and, within some conditions, needs to retrieve the data pointed by the REF value.
And here lies the problem.
We tried:
SQL> SELECT DEREF(:ref) FROM DUAL
  2  /
SELECT DEREF(:ref) FROM DUAL
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected REF got CHAR


SQL> SELECT DEREF(HEXTORAW(:ref)) FROM DUAL
  2  /
SELECT DEREF(HEXTORAW(:ref)) FROM DUAL
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected REF got BINARY
We then desperately tried CAST and TREAT functions but we already knew we are out the scope of these ones:
SQL> SELECT DEREF(CAST(HEXTORAW(:ref) AS REF emp_person_typ)) FROM dual
  2  /
SELECT DEREF(CAST(HEXTORAW(:ref) AS REF emp_person_typ)) FROM dual
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected REF got BINARY


SQL> SELECT DEREF(TREAT(HEXTORAW(:ref) AS REF emp_person_typ)) FROM dual
  2  /
SELECT DEREF(TREAT(HEXTORAW(:ref) AS REF emp_person_typ)) FROM dual
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got BINARY

And here's the question
Does anyone know a way to fulfill this task or workaround this issue?


Cross-ref: https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=96446

[Updated on: Sat, 21 August 2021 03:38]

Report message to a moderator

Re: About REF datatype [message #684785 is a reply to message #684783] Fri, 20 August 2021 16:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3092
Registered: January 2010
Location: Connecticut, USA
Senior Member
I don't think you can do this via SQL*Plus variable. Can you do it in PL/SQL?

SET SERVEROUTPUT ON
DECLARE
    V_EMP_PERSON_REF REF EMP_PERSON_TYP;
    V_EMP_PERSON     EMP_PERSON_TYP;
BEGIN
    SELECT  MANAGER
      INTO  V_EMP_PERSON_REF
      FROM  EMP_PERSON_OBJ_TABLE
      WHERE NAME = 'Bob Jones';
-- Now the application makes some work and, within some conditions, needs to retrieve the data pointed by the REF value.
    SELECT  DEREF(V_EMP_PERSON_REF)
      INTO  V_EMP_PERSON
      FROM  DUAL;
    DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
END;
/
Name is John Smith

PL/SQL procedure successfully completed.

SQL>
SY.
Re: About REF datatype [message #684786 is a reply to message #684785] Sat, 21 August 2021 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for your answer, Solomon.

I can't do it this way: the application has to store the REF value in a variable to use it later depending on some conditions computed between the moment it gets the REF value and the moment it has to use some parts of the referenced one.
These computations can't be done in one shot, as in a PL/SQL block, but depend on interactions with other processes, it's a workflow.
More, these processes used different programming languages (then the use of a string to store the REF value).

What would be nice, is a SQL or PL/SQL function like OCIRefFromHex in OCI.

[Updated on: Sat, 21 August 2021 10:48]

Report message to a moderator

Re: About REF datatype [message #684792 is a reply to message #684786] Sun, 22 August 2021 11:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3092
Registered: January 2010
Location: Connecticut, USA
Senior Member
Can you save ROWID of the REF?

SQL> VARIABLE V_RID VARCHAR2(100)
SQL> BEGIN
  2      SELECT  ROWID
  3        INTO  :V_RID
  4        FROM  EMP_PERSON_OBJ_TABLE
  5        WHERE NAME = 'Bob Jones';
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> -- Now the application makes some work and, within some conditions,
SQL> -- needs to retrieve the data pointed by the REF value.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4      SELECT  DEREF(MANAGER)
  5        INTO  V_EMP_PERSON
  6        FROM  EMP_PERSON_OBJ_TABLE
  7        WHERE ROWID = :V_RID;
  8      DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  9  END;
 10  /
Name is John Smith

PL/SQL procedure successfully completed.

SQL>
SY.
Re: About REF datatype [message #684793 is a reply to message #684792] Sun, 22 August 2021 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It's a good idea but not practicable in our case: the workflow may last days (and even several weeks in case of holidays) and we can't lock the row for such a long time as other processes may update other part(s) of the row (the model is in 3NF and I agree it should be in 4NF in this case), and an unlocked row may see its rowid changed (seldom but can happen and we can't afford a solution which will sometimes fail).

However, I will keep this in mind, in case we have no other solution.

(Another solution which has been proposed is to retrieve and copy the whole pointed data but, in addition to the fact these data may have dozens of MB and may have to be transmitted over very slow networks, they (given the duration of the workflow) may not be up to date when they have to be used.)

Re: About REF datatype [message #684794 is a reply to message #684793] Sun, 22 August 2021 13:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3092
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Sun, 22 August 2021 13:08

It's a good idea but not practicable in our case: the workflow may last days (and even several weeks in case of holidays) and we can't lock the row for such a long time as other processes may update other part(s) of the row (the model is in 3NF and I agree it should be in 4NF in this case), and an unlocked row may see its rowid changed (seldom but can happen and we can't afford a solution which will sometimes fail).
I think the above logic is flawed - it relies on a REF to object that technically doesn't exist anymore and we have do idea when and how oracle does the cleanup. It could be UNDO based and if so we risk getting "snapshot too old" or it could be completely internal to Oracle and can change in the future so we can't rely on it.

SY.
Re: About REF datatype [message #684795 is a reply to message #684794] Sun, 22 August 2021 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not UNDO based, the REF format is (partially) documented (although this is spread across the doc and the information is not completely correct and sometimes conflicting):

Quote:
A REF contains the following three logical components:
- OID of the object referenced. A system-generated OID is 16 bytes long. The size
of a primary-key based OID depends on the size of the primary key column(s).
- OID of the table or view containing the object referenced, which is 16 bytes long.
- Rowid hint, which is 10 bytes long.
Quote:
When the database constructs a REF to a row object, the constructed REF is made
up of the object identifier (OID), some metadata of the object table, and,
optionally, the ROWID.
The size of a REF in a column of REF type depends on the storage requirements
associated with the column, as follows:
- If the column is declared as a REF WITH ROWID, the database stores the ROWID in
the REF column. The ROWID hint is ignored for object references in constrained
REF columns.
- If a column is declared as a REF with a SCOPE clause, the column decreases due
to the omission of the object table metadata and the ROWID.
A scoped REF is 16 bytes long.
...

In short, it is a logical pointer to a row object, containing information to prevent from accessing other data than the originally referenced ones.
There is no cleanup, when trying to access the referenced object, Oracle verifies it still exists using the OID and other stuff inside the REF value and if it doesn't raise a "dangling" exception (ORA-22881, OA-22919, you can avoid the exception using the predicate "IS NOT DANGLING").


But, going back to your previous post, I thought about a new option: instead of passing the REF value, storing it in a working table with a system generated (number) primary key which will be passed across the workflow, then the different processes can query this working table to get and use the data pointed by the REF value (I prefer the use of a PK over a ROWID).

Re: About REF datatype [message #684797 is a reply to message #684795] Sun, 22 August 2021 14:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3092
Registered: January 2010
Location: Connecticut, USA
Senior Member
I understand, but you are taking a gamble. Assume we captured REF TO Bob Jones's manager - pointer to John Smith. Then Sam Elliot becomes Bob Jones's manager and John Smith retires and is deleted from the table. Now look what happens when we try to DEREF:

SQL> CREATE OR REPLACE
  2    PACKAGE REF_VARIABLES
  3      IS
  4        G_EMP_PERSON_REF REF EMP_PERSON_TYP;
  5  END;
  6  /

Package created.

SQL> BEGIN
  2      SELECT  MANAGER
  3        INTO  REF_VARIABLES.G_EMP_PERSON_REF
  4        FROM  EMP_PERSON_OBJ_TABLE
  5        WHERE NAME = 'Bob Jones';
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4      SELECT  DEREF(REF_VARIABLES.G_EMP_PERSON_REF)
  5        INTO  V_EMP_PERSON
  6        FROM  DUAL;
  7      DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  8  END;
  9  /
Name is John Smith

PL/SQL procedure successfully completed.

SQL> -- Now Bob Jones's manager is changed to Sam Elliot and
SQL> -- Jon Smith is removed since he is no longer with the company.
SQL>
SQL> INSERT
  2    INTO EMP_PERSON_OBJ_TABLE
  3    VALUES(
  4           EMP_PERSON_TYP('Sam Elliot',NULL)
  5          )
  6  /

1 row created.

SQL> UPDATE EMP_PERSON_OBJ_TABLE
  2     SET MANAGER = (
  3                    SELECT  REF(E)
  4                      FROM  EMP_PERSON_OBJ_TABLE E
  5                      WHERE NAME = 'Sam Elliot'
  6                   )
  7   WHERE NAME = 'Bob Jones'
  8  /

1 row updated.

SQL> DELETE  EMP_PERSON_OBJ_TABLE
  2    WHERE NAME = 'John Smith'
  3  /

1 row deleted.

SQL> DECLARE
  2      V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4      SELECT  DEREF(REF_VARIABLES.G_EMP_PERSON_REF)
  5        INTO  V_EMP_PERSON
  6        FROM  DUAL;
  7      DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  8  END;
  9  /
Name is

PL/SQL procedure successfully completed.

SQL>

As you can see REF is now pointing to nowhere (dangling is Oracle used term).

SY.
Re: About REF datatype [message #684798 is a reply to message #684797] Sun, 22 August 2021 15:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3092
Registered: January 2010
Location: Connecticut, USA
Senior Member
Another example - Bob Jones's manager changed name:

SQL> BEGIN
  2      SELECT  MANAGER
  3        INTO  REF_VARIABLES.G_EMP_PERSON_REF
  4        FROM  EMP_PERSON_OBJ_TABLE
  5        WHERE NAME = 'Bob Jones';
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4      SELECT  DEREF(REF_VARIABLES.G_EMP_PERSON_REF)
  5        INTO  V_EMP_PERSON
  6        FROM  DUAL;
  7      DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  8  END;
  9  /
Name is John Smith

PL/SQL procedure successfully completed.

SQL> UPDATE EMP_PERSON_OBJ_TABLE SET NAME = 'Peter Pan' WHERE NAME = 'John Smith';

1 row updated.

SQL> DECLARE
  2      V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4      SELECT  DEREF(REF_VARIABLES.G_EMP_PERSON_REF)
  5        INTO  V_EMP_PERSON
  6        FROM  DUAL;
  7      DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  8  END;
  9  /
Name is Peter Pan

PL/SQL procedure successfully completed.

SQL>
This clearly shows REF is just a pointer and makes no difference when was it saved - DEREF will always show current data, not data at the time REF was saved.

SY.
Re: About REF datatype [message #684799 is a reply to message #684797] Sun, 22 August 2021 15:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quite strange, I think DEREF bug is gobbling up the error, maybe is it expected to get NULL instead of an exception with DEREF, nothing is said in the doc.

We actually don't use DEREF but UTL_REF package and the result is different:
SQL> DECLARE
  2    V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4    utl_ref.select_object(REF_VARIABLES.G_EMP_PERSON_REF,V_EMP_PERSON);
  5    DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SYS.UTL_REF", line 3
ORA-06512: at line 4
(with the previous value:
SQL> rollback;

Rollback complete.

SQL> DECLARE
  2    V_EMP_PERSON         EMP_PERSON_TYP;
  3  BEGIN
  4    utl_ref.select_object(REF_VARIABLES.G_EMP_PERSON_REF,V_EMP_PERSON);
  5    DBMS_OUTPUT.PUT_LINE('Name is ' || V_EMP_PERSON.NAME);
  6  END;
  7  /
Name is John Smith

PL/SQL procedure successfully completed.
)
Re: About REF datatype [message #684800 is a reply to message #684799] Sun, 22 August 2021 15:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3092
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, in any case we don't get data as it was at the time REF was captured.

SY.
Re: About REF datatype [message #684801 is a reply to message #684800] Mon, 23 August 2021 01:15 Go to previous message
Michel Cadot
Messages: 68010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for this discussion, it will conduct our tests to prevent from unexpected results and errors.

Previous Topic: Oracle INSERT, SELECT, NOT EXISTS primary key
Next Topic: Generate Unique Grouping ID
Goto Forum:
  


Current Time: Wed Oct 27 14:35:01 CDT 2021