CREATE OR REPLACE TYPE period AS OBJECT(
name VARCHAR2(30)
) NOT FINAL;


CREATE OR REPLACE TYPE period_art UNDER period(
style VARCHAR2(30)
);


CREATE TABLE periods OF period;


INSERT into periods VALUES(
period_art(
'period_art',
'style'
));


CREATE OR REPLACE TYPE address AS OBJECT(
street VARCHAR2(30),
code  VARCHAR2(10),
city  VARCHAR2(30)
);

CREATE OR REPLACE TYPE contact AS OBJECT(
phone VARCHAR2(15),   
e_mail   VARCHAR2(50)     
);


CREATE OR REPLACE TYPE person AS OBJECT(
name VARCHAR2(20),
surname VARCHAR2(50),
MEMBER FUNCTION  identify RETURN VARCHAR2
)NOT FINAL;


CREATE OR REPLACE TYPE artist UNDER person(
country VARCHAR2(30),
OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2
) NOT INSTANTIABLE NOT FINAL;


CREATE OR REPLACE TYPE painter UNDER artist(
style_painter period_art,
OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2
);
CREATE TYPE BODY painter AS
  OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2 IS
   BEGIN
      RETURN 'painter';
    END;
END;

CREATE TABLE painters OF painter;


INSERT INTO painters VALUES (
painter
(
'painter name',
'painter surname',
'country',
period_art('XXw', 'Impresjonizm')));

INSERT INTO painters VALUES (
painter
(
'Lars',
'Vinyl',
'country',
period_art('XXw', 'Impresjonizm')));



CREATE OR REPLACE TYPE owner UNDER person(
pesel VARCHAR2(11),
wl_address ADDRESS,
wl_contact CONTACT
) NOT FINAL;

CREATE TABLE tabowner of owner;


insert into tabowner values (
owner( 'Peter', 'Smith', '5555555555' ,
address('street', '35-056' , 'Cracov'),
contact ('785654123', 'tome3@gmail.com')));

insert into tabowner values (
owner( 'John', 'Malkovic', '8888888888' ,
address('Green', '35-056' , 'Warsaw'),
contact ('698564231', 'trunk@gmail.com')));


CREATE OR REPLACE TYPE exhibit AS OBJECT(
sygnatura VARCHAR2(30),
name VARCHAR2(100),
picture ORDIMAGE,
who REF owner,
MEMBER FUNCTION identify RETURN VARCHAR2
) NOT FINAL;

CREATE TABLE exhibits OF exhibit;


CREATE OR REPLACE TYPE exhibit_culture UNDER exhibit(
author REF painter,
OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2
) NOT FINAL;

CREATE OR REPLACE TYPE painting UNDER exhibit_culture(
technician VARCHAR2(50),
OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2
);


CREATE TYPE BODY painting AS
  OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2 IS
   BEGIN
      RETURN 'painting';
    END;
END;



INSERT INTO exhibits VALUES( 
painting(
'Eks/K/O/1256',
'portrait mary',
ordimage.init(),
(SELECT REF(k) FROM tabowner k WHERE k.name= 'Peter' and k.surname= 'Smith'),
(SELECT REF(p) FROM painters p WHERE p.name = 'painter name' AND p.surname = 'painter surname' ),
'Oil') 
);


INSERT INTO exhibits VALUES( 
painting(
'Eks/K/O/1257',
'portrait peter',
ordimage.init(),
(SELECT REF(k) FROM tabowner k WHERE k.name= 'John' and k.surname= 'Malkovic'),
(SELECT REF(p) FROM painters p WHERE p.name = 'Lars' AND p.surname = 'Vinyl' ),
'Oil') 
);

drop table exhibits;
drop type painting;
drop type exhibit_culture;
drop type exhibit;
drop table tabowner;
drop type owner;
drop table painters;
drop type painter;
drop type artist;
drop type person;
drop type contact;
drop type address;
drop table periods;
drop type period_art;
drop type period;

select e.sygnatura, e.name , e.who.name as Name_Owner, 
e.who.surname as Surname_Owner,
e.who.pesel as Pesel_Owner, e.who.wl_address.street as Street_Owner, 
e.who.wl_address.code as Code_Owner, e.who.wl_address.city as City_Owner
from exhibits  e
where e.sygnatura ='Eks/K/O/1257' and value(e) is of (painting);

SCOTT@orcl_11g> select e.sygnatura, e.name , e.who.name as Name_Owner, e.who.surname as Surname_Owner,
  2  	    e.who.pesel as Pesel_Owner, e.who.wl_address.street as Street_Owner,
  3  	    e.who.wl_address.code as Code_Owner, e.who.wl_address.city as City_Owner,
  4  	    TREAT (VALUE (e) AS painting).author.name,
  5  	    TREAT (VALUE (e) AS painting).author.surname,
  6  	    TREAT (VALUE (e) AS painting).author.country,
  7  	    TREAT (VALUE (e) AS painting).author.style_painter
  8  from   exhibits e
  9  where  e.sygnatura = 'Eks/K/O/1257'
 10  and    value(e) is of (painting);

SYGNATURA
------------------------------
NAME
--------------------------------------------------------------------------------
NAME_OWNER           SURNAME_OWNER
-------------------- --------------------------------------------------
PESEL_OWNER STREET_OWNER                   CODE_OWNER
----------- ------------------------------ ----------
CITY_OWNER                     TREAT(VALUE(E)ASPAIN
------------------------------ --------------------
TREAT(VALUE(E)ASPAINTING).AUTHOR.SURNAME
--------------------------------------------------
TREAT(VALUE(E)ASPAINTING).AUTH
------------------------------
TREAT(VALUE(E)ASPAINTING).AUTHOR.STYLE_PAINTER(NAME, STYLE)
--------------------------------------------------------------------------------
Eks/K/O/1257
portrait peter
John                 Malkovic
8888888888  Green                          35-056
Warsaw                         Lars
Vinyl
country
PERIOD_ART('XXw', 'Impresjonizm')


SCOTT@orcl_11g>