Retrieve objects in Oracle

Question

I have a Oracle object called Menu which has a object type as an attribute.

menu type

Create Type Menu as Object( beer REF beertype, price float )

beertype

Create Type beertype as object( name char(20), seller char(20) )

table created using menu type

Create table Sells of Menu

table created using beertype

Create table beer of beertype

Now what I want to do is I want to write a select query which retrieves the name of the beer which has price more than 3.5.

I tried

SELECT beer.name 
FROM Sells; 

and

SELECT Sells.beer.name 
FROM Sells; 

Answer 1

Here's an example: test case first:

SQL> create type beertype as object(
  2    name char(20),
  3    seller char(20)
  4  );
  5  /

Type created.

SQL> create type menu as object(
  2    beer ref beertype,
  3    price float
  4  );
  5  /

Type created.

SQL> create table sells of menu;

Table created.

SQL> create table beer of beertype;

Table created.

SQL> insert into beer (name, seller) values ('Heineken', 'KTC');

1 row created.

SQL> insert into beer (name, seller) values ('Tuborg', 'Plodine');

1 row created.

SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Heineken'), 10);

1 row created.

SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Tuborg'), 2.5);

1 row created.

Query which selects beer name whose price is larger than 3.5:

SQL> select b.name, s.price
  2  from sells s join beer b on value(b) = deref(s.beer)
  3  where s.price > 3.5;

NAME                      PRICE
-------------------- ----------
Heineken                     10

SQL>

Here's a nice article, if you want to read more: Using REF Values for Retrieval of Data Values

Answer 2

You don't need to use a JOIN to get the name; you can just use DEREF or chain the reference/attribute names in the SELECT clause:

select DEREF(s.beer).name AS name, s.price
from   sells s
where  s.price > 3.5;

or

select s.beer.name AS name, s.price
from   sells s
where  s.price > 3.5;

(The first option makes it clear that you are de-referencing a REF whereas the second option is more concise.)

Which outputs:

NAME                 | PRICE
:------------------- | ----:
Heineken             |    10

db<>fiddle here