Как получить значение атрибута объекта из коллекции REF элементов?

Question

Пытаюсь получить доступ к атрибуту объекта из коллекции REF элементов.

Имеется такая структура:

create or replace type o1 as object (id number, code varchar2 (32));
/
create table t1 of o1 (id primary key);

create type reflist as varray (5) of ref o1
/
create table t2 (id number primary key, refs reflist);

Проблема в том, что когда делаю запрос к t2, не знаю как получить занчения из колонки типа VARRAY of REF.

Пробовал, например, так:

select t2.id, r.code
from t2, table (t2.refs) r;

Error report - SQL Error: ORA-00904: "R"."CODE": invalid identifier

Тестовые данные:

insert into t1 values (1, 'code-1');
insert into t1 values (2, 'code-2');
insert into t2
    select 1, reflist (ref(t1), ref(t2))
    from t1 t1, t1 t2 where t1.id=1 and t2.id=2;

_{Свободный перевод вопроса ORACLE - select data from VARRAY OF REF Object от участника @Francisco Belda}

Answer 1

Результат выражения TABLE в этом случае будет только одна псевдоколонка с типом COLUMN_VALUE, так как REF это скалярный тип данных. Выдержка из документации:

COLUMN_VALUE Pseudocolumn
[...] or when you use the TABLE collection expression to refer to a scalar nested table type, the database returns a virtual table with a single column. This name of this pseudocolumn is COLUMN_VALUE.

Запрос будет выглядеть вот так:

select t2.id, r.column_value.code code, r.column_value ref
from t2, table (t2.refs) r;

        ID CODE     REF                             
---------- -------- --------------------------------
         1 code-1   280209B2343D5B5A2E4725E0530A01A8
                    C0279AB2343D5B5A254725E0530A01A8
                    C0279A030010950000              
         1 code-2   280209B2343D5B5A2F4725E0530A01A8
                    C0279AB2343D5B5A254725E0530A01A8
                    C0279A030010950001

Если коллекция может быть пустой или NULL, то надо воспользоваться OUTER связью таблиц:

insert into t2
    select 2, reflist () from dual;

select t2.id, r.pos, r.val.code
from t2 outer apply ( 
    select rownum pos, column_value val 
    from table (t2.refs)) r;

        ID        POS VAL.CODE                        
---------- ---------- --------------------------------
         1          1 code-1                          
         1          2 code-2                          
         2 null       null

БЛОГ НА HUSL

Как получить значение атрибута объекта из коллекции REF элементов?

Ответы (1 шт):