No question at this time
DBA Top 10
1 M. Cadot 6700
2 B. Vroman 3900
3 A. Kavsek 2700
4 J. Péran 1700
5 T. Boles 600
5 D. Johnson 600
7 T. P 500
8 P. Wisse 400
9 S. Spb 300
10 V. Gupta 200
10 M. Jain 200
10 K. Pingeli 200
10 R. Pastre 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48649
Total active users1380
Act. users last 24h1
Act. users last hour0
Registered user hits last week81
Registered user hits last month279
Go up

About REF datatype
Next thread: active dataguard usage
Prev thread: date, when the Role is delete from db

Message Score Author Date
<b>Background</b> We have an application which ...... Michel Cadot Aug 21, 2021, 10:36
Cross-ref: http://www.orafaq.com/forum/t/207717/...... Michel Cadot Aug 21, 2021, 10:38
Hi Michel, Maybe you could cheat a little :)...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Joël Péran Aug 23, 2021, 11:48
Thanks for your answer Joël, as you suspected it...... Michel Cadot Aug 23, 2021, 12:31
Ok :) As it was suggested in the other forum, it ...... Joël Péran Aug 23, 2021, 13:43

Follow up by mail Click here


Subject: About REF datatype
Author: Michel Cadot, France
Date: Aug 21, 2021, 10:36, 108 days ago
Oracle info: 10.2 to 19
Message: 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?


Regards
Michel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About REF datatype
Author: Michel Cadot, France
Date: Aug 21, 2021, 10:38, 108 days ago
Message:
Cross-ref: http://www.orafaq.com/forum/t/207717/

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About REF datatype
Author: Joël Péran, France
Date: Aug 23, 2021, 11:48, 106 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Michel,

Maybe you could cheat a little :) by adressing the field of the REFerence directly ? Like Manager.name to get the name of the manager ? But as this is a recursive thing, maybe you can't ?

Best regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About REF datatype
Author: Michel Cadot, France
Date: Aug 23, 2021, 12:31, 106 days ago
Message:
Thanks for your answer Joël, as you suspected it is not possible.
I provided a very simple example to show the problem but the real case is actually far much complex:
not only it is recursive but the objects contain many fields/attributes which may be of any kind of
type including REF themselves.

This is a workflow involving many processes and each of them can only access some specific parts of
the referenced data (due to GDPR). (This is done via procedures specific to each process.)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: About REF datatype
Author: Joël Péran, France
Date: Aug 23, 2021, 13:43, 106 days ago
Message: Ok :)
As it was suggested in the other forum, it may only work with PL/SQL but I really doubt that you do not use PL/SQL programs :)
This is an interesting case and I will keep an eye on it :)
Best regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here