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;