Oracle: How to DEREF a VARRAY of REFs and NESTED TABLE of REFs?

Question

I'm dealing with Nested Tables and VArrays in Oracle (just to clarify, it's for learning purposes at university). I have this TABLE structure, and everything it's OK when I put the data in.

CREATE OR REPLACE TYPE Addendum AS OBJECT (
    idCode          VARCHAR2(10),
    signature_date  TIMESTAMP,
    --A VARRAY(5) OF REF STUDENT (STUDENT IF THE TYPE, TSTUDENT THE TABLE)
    studentlist     student_list,
    --A TABLE OF REF STAFF (STAFF IS THE TYPE, TSTAFF THE TABLE)
    staff_members   tStaff,
    pdi_manager     REF PDI
) FINAL;

CREATE TABLE Addendums OF Addendum
    NESTED TABLE staff_members STORE AS staffmembers_nt;
CREATE UNIQUE INDEX ADDENDUMS_IDCODE_UINDEX on ADDENDUMS (IDCODE);

CREATE OR REPLACE TYPE tAddendum AS TABLE OF Addendum;

In a first try, I execute a query like SELECT * FROM ADDENDUMS and it shows me this thing:

0000000000,2019-09-01 00:00:00.000000,"{<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF}","{<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF}"
0000000001,2019-09-01 00:00:00.000000,"{<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF}","{<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF,<failed to load> oracle.sql.REF}"

Also I tried to select with DEREF instruction and referencing explicitly the columns, with: select ad.IDCODE, ad.SIGNATURE_DATE, DEREF(ad.STUDENTLIST), DEREF(ad.STAFF_MEMBERS), DEREF(ad.PDI_RESPONSABLE) from UOC.ADDENDUMS ad; and it shows only the PDI_MANAGER dereferenced correctly.

What can I do with VARRAY and NESTED TABLE? Like, what is the correct syntax for SQL (not PL/SQL like in this question)?

Answer 1

You can CROSS JOIN the VARRAY (or nested table) using a table collection expression and then DEREF(VALUE(collection_alias)).column_name to get the value.

For example:

SELECT a.idcode,
       a.signature_date,
       DEREF(VALUE(s)).id AS id,
       DEREF(VALUE(s)).name AS name
FROM   ADDENDUMS a
       CROSS JOIN TABLE( a.studentlist ) s;

Which, for your types:

CREATE TYPE Student AS OBJECT(
  id NUMBER,
  name VARCHAR2(50)
);

CREATE TABLE students OF Student (
  CONSTRAINT students__id__pk PRIMARY KEY ( id )
);

CREATE TYPE student_list AS VARRAY(5) OF REF Student;

CREATE OR REPLACE TYPE Addendum AS OBJECT (
    idCode          VARCHAR2(10),
    signature_date  TIMESTAMP,
    --A VARRAY(5) OF REF STUDENT (STUDENT IF THE TYPE, TSTUDENT THE TABLE)
    studentlist     student_list
) FINAL;

CREATE TABLE Addendums OF Addendum;

and some sample data:

BEGIN
  INSERT INTO Students ( id, name ) VALUES ( 1, 'AAA' );
  INSERT INTO Students ( id, name ) VALUES ( 2, 'BBB' );
  INSERT INTO Students ( id, name ) VALUES ( 3, 'CCC' );
  INSERT INTO Students ( id, name ) VALUES ( 4, 'DDD' );
  INSERT INTO Students ( id, name ) VALUES ( 5, 'EEE' );
  INSERT INTO Students ( id, name ) VALUES ( 6, 'FFF' );

  INSERT INTO Addendums ( idCode, signature_date, studentlist )
  VALUES (
    1,
    SYSTIMESTAMP,
    student_list(
      ( SELECT REF(s) FROM students s WHERE id = 2 ),
      ( SELECT REF(s) FROM students s WHERE id = 4 ),
      ( SELECT REF(s) FROM students s WHERE id = 1 )
    )
  );
END;
/

Outputs:

IDCODE | SIGNATURE_DATE               | ID | NAME
:----- | :--------------------------- | -: | :---
1      | 01-OCT-20 05.01.00.699863 PM |  2 | BBB 
1      | 01-OCT-20 05.01.00.699863 PM |  4 | DDD 
1      | 01-OCT-20 05.01.00.699863 PM |  1 | AAA 

db<>fiddle here

(Note: No sample code was given for the type TStaff so I've cut that bit out; however the method is the same as for a VARRAY.)

---

In later Oracle versions (that support the OFFSET m ROWS FETCH NEXT n ROWS syntax) you could use nested queries to get the value from the VARRAY; however, this isn't supported in Oracle 11g:

SELECT a.idcode,
       a.signature_date,
       ( SELECT DEREF(VALUE(t)).name
         FROM   TABLE( a.studentlist ) t
         OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY ) AS name1,
       ( SELECT DEREF(VALUE(t)).name
         FROM   TABLE( a.studentlist ) t
         OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY ) AS name2,
       ( SELECT DEREF(VALUE(t)).name
         FROM   TABLE( a.studentlist ) t
         OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY ) AS name3,
       ( SELECT DEREF(VALUE(t)).name
         FROM   TABLE( a.studentlist ) t
         OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY ) AS name4,
       ( SELECT DEREF(VALUE(t)).name
         FROM   TABLE( a.studentlist ) t
         OFFSET 4 ROWS FETCH NEXT 1 ROW ONLY ) AS name5
FROM   ADDENDUMS a;

Outputs:

IDCODE | SIGNATURE_DATE            | NAME1 | NAME2 | NAME3 | NAME4 | NAME5
:----- | :------------------------ | :---- | :---- | :---- | :---- | :----
1      | 01-OCT-20 17.09.08.790538 | BBB   | DDD   | AAA   | null  | null 

db<>fiddle here