Why doesn't the REF function in SQL return a reference, but an object instance in Oracle sql developer?

45 views Asked by At

REF and VALUE are two reference related functions in SQL. According to the definition on Oracle Database Application Developer’s Guide - Object-Relational Features

  • "The REF function in a SQL statement takes as an argument a correlation name (or table alias) for an object table or view and returns a reference (a REF) to an object instance from that table or view"
  • "the VALUE function takes as its argument a correlation variable (table alias) for an object table or object view and returns object instances corresponding to rows of the table or view"

Unfortunately, under Oracle SQL Developer, the REF function returned an object instance, rather than a reference.

The testing examples I used are from

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/REF.html#GUID-B7622138-6EB6-4203-B5E7-91CAD52E9DB1

However, when I ran the same statements under SQL Plus, it returned a reference, as expected.

Does anyone know why this happens under Oracle SQL Developer?

Thanks in advance.

Richard

The testing examples I used are from

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/REF.html#GUID-B7622138-6EB6-4203-B5E7-91CAD52E9DB1

drop type cust_address force;
create type cust_address as object
(STREET                    VARCHAR2(40),
POSTAL_CODE                VARCHAR2(10),
CITY                       VARCHAR2(30),
COUNTRY                    VARCHAR2(20)
)

 CREATE TABLE addresses OF cust_address_typ;

INSERT INTO addresses VALUES (
   '123 First Street', '4GF H1J', 'Our Town', 'Ourcountry');

SELECT REF(e) FROM addresses e;

Under Oracle SQL Developer, the REF function returned an object instance, rather than a reference, the same as the use of VALUE function.

SYSTEM.CUST_ADDRESS_TYP('123 First Street', '4GF H1J', 'Our Town', 'Ourcountry')

Under SQL Plus, the same REF statement returned a reference, as expected.

0

There are 0 answers