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;

Littlefoot · Accepted Answer · 2018-08-14 06:33:35Z

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

answered Aug 14, 2018 at 6:33

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ramesh Over a year ago

It works, I wanna know one more thing. Is it possible to write select query without using join in my case?

Littlefoot Over a year ago

As far as I can tell, no (if you want to select both NAME and PRICE).

Ramesh Over a year ago

SELECT s.beer.name, s.price FROM Sells s; I just found an article mentioning something like this. Currently I am unable to check whether it works or not. What's your opinion

Ramesh Over a year ago

That's great news for me since I am not that much familiar with join. Thank you anyway ;-)

Littlefoot Over a year ago

You're welcome; I'm glad if you'll manage to solve the problem.

MT0 · Accepted Answer · 2019-11-13 11:44:35Z

0

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

edited Nov 13, 2019 at 11:44

answered Nov 13, 2019 at 11:39

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Retrieve objects in Oracle

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related