ORACLE - select data from VARRAY OF REF Object

Question

I am using ORACLE and want to access an Object of an array with REF. This is my Structure:

create type articulo as object (
    codigo_barras number,
    nombre varchar2(100),
    precio number,
    categoria varchar2(100),
    stock number
                               );

create table almacen of articulo(
    codigo_barras primary key
    );

create table estanteria(
    identificador varchar2(10) primary key ,
    pasillo number,
    seccion character,
    producto ref articulo scope is almacen
);

create type lista as VARRAY(5) of REF articulo;

create table escaparate(
  tablon number primary key ,
  lista_productos lista
);

insert into almacen values (1,'Destornillador',5,'Herramientas',20);
insert into almacen values (2,'Llave inglesa',12,'Herramientas',30);
insert into almacen values (3,'Martillo',16,'Herramientas',20);
insert into almacen values (4,'Soplete',24,'Maquinaria',5);
insert into almacen values (5,'Radial',35,'Maquinaria',10);

insert into estanteria
select 1,1,'A',ref(a)
from almacen a
where a.CODIGO_BARRAS=1;

insert into estanteria
select 2,3,'B',ref(a)
from almacen a
where a.CODIGO_BARRAS=4;

insert into escaparate
select 1,lista(REF(a1),REF(a2))
FROM almacen a1, almacen a2
where a1.CODIGO_BARRAS=1 and a2.CODIGO_BARRAS=2;

The problem i s when i try to access escaparate, can't reach data from the VARRAY of REFS. Tried this:

select e.tablon, t.nombre
from escaparate e, table( e.lista_productos ) t;

score 0 · Answer 1 · answered Nov 08 '20 at 12:19

A table collection expression returns the data with the COLUMN_VALUE identifier, you can use this to get the REF value:

SELECT e.tablon,
       t.COLUMN_VALUE.nombre AS nombre
FROM   escaparate e
       LEFT OUTER JOIN table( e.lista_productos ) t
       ON ( 1 = 1 );

(You can also LEFT OUTER JOIN so that you will get rows if the VARRAY is empty or NULL.)

So, for the data:

insert into escaparate ( tablon, lista_productos )
VALUES (
  1,
  lista(
    ( SELECT REF(a) FROM almacen a WHERE CODIGO_BARRAS=1 ),
    ( SELECT REF(a) FROM almacen a WHERE CODIGO_BARRAS=2 )
  )
);

insert into escaparate ( tablon, lista_productos )
VALUES ( 2,lista() );

insert into escaparate ( tablon, lista_productos )
VALUES ( 3, NULL );

This outputs:

TABLON | NOMBRE        
-----: | :-------------
     1 | Destornillador
     1 | Llave inglesa 
     2 | null          
     3 | null

Or, if you want the position in the array, you can use a LATERAL join:

SELECT e.tablon,
       t.*
FROM   escaparate e
       LEFT OUTER JOIN LATERAL (
         SELECT ROWNUM AS rn,
                t.COLUMN_VALUE.nombre AS nombre
         FROM   TABLE( e.lista_productos ) t
       ) t
       ON ( 1 = 1 );

Which outputs:

TABLON |   RN | NOMBRE        
-----: | ---: | :-------------
     1 |    1 | Destornillador
     1 |    2 | Llave inglesa 
     2 | null | null          
     3 | null | null

db<>fiddle here

You can simplify the last example, by using `OUTER APPLY` instead of `LEFT OUTER JOIN LATERAL` - that way you won't need the redundant `ON` condition. — , Nov 08 '20 at 15:14

ORACLE - select data from VARRAY OF REF Object

1 Answers1