CODEWITHSUNDEEP
sqloracle-databaseplsql
Arthur Romantsov
Arthur Romantsov

Reputation: 27

How to view user object type in table? (pl/sql)

In my programm I need to make own object type - fraction.
I can create it like: 

 CREATE OR REPLACE TYPE fraction AS OBJECT (   integer number,     numerator number,    denominator number);

Can create table with columns like fraction

 CREATE TABLE own_table (first fraction, second fraction);

It's all o.k. How to make own view of column like fraction? May be some methods or anything else? Column have type only my fraction. Nessesary to change only view in table for user.

requered result: 

SELECT * FROM own_table;

+first +++++ second  
1(1/2) ++++  2(49/203)  
3(32/34) ++ 12(3/7)

Upvotes: 1

Views: 239

Answers (1)

Gerrat
Gerrat

Reputation: 29700

You can just use dotted notation to access the object members, but it appears you have to use an alias for the table name when selecting.

You also cannot name a column integer.

You'll need a view on your table if you just want to do a select * from something. eg.

CREATE OR REPLACE own_view (first, second) AS 
SELECT ot.first.i || '(' || ot.first.numerator || 
'/' || ot.first.denominator || ')',
ot.second.i || '(' || ot.second.numerator || 
'/' || ot.second.denominator || ')' 
FROM own_table ot;

Fiddle here.

Also, some docs on accessing Oracle object types.

Upvotes: 2

Related Questions