ORACLE OBJECT TABLES

We have defined book_type in this post User Defined Types

DESC book_type

--here our type definition
user type definition

-------------------------------------
TYPE  book_type  AS OBJECT (

ISBN_no NUMBER(12),
published_year NUMBER(4)
) NOT FINAL
4 rows selected

--now we are creating an object table which consists many book_types in it
--object tables can only store types in it there are VARCHARS,NUMBERS in it
CREATE TABLE object_table_books OF book_type;

--However we can reference an object table in our standard table with REF
-- keyword and SCOPE keyword
CREATE TABLE borrows (
 borrow_id  NUMBER PRIMARY KEY,
 borrower VARCHAR2(100),
 book REF book_type  SCOPE IS object_table_books -- reference to an object table
);

INSERT INTO object_table_books
VALUES (book_type(1111111,2009));
--we can insert book_type in this object table
--with this syntax
1 rows inserted.
INSERT INTO object_table_books
VALUES (book_type(2222222,2009));
1 rows inserted.

SELECT * FROM object_table_books;
--get newly inserted data 

ISBN_NO                PUBLISHED_YEAR
---------------------- ----------------------
1111111                2009
2222222                2009                   

2 rows selected

INSERT INTO borrows (borrow_id, borrower, book)
VALUES (1,'ANUTWALIDERA',
(SELECT REF(otb) FROM object_table_books otb WHERE isbn_no = 1111111)
); -- we must use REF keyword to map a book_type between borrow and object_table_books
--think this as a foreign key
1 rows inserted.

SELECT borrow_id, borrower,DEREF(book).isbn_no FROM borrows;

BORROW_ID              BORROWER                    DEREF(BOOK).ISBN_NO
---------------------- --------------------------- ----------------------
1                      ANUTWALIDERA                1111111                

1 rows selected

--when selecting data from a standard table which contains a REF column data
-- we should use DEREF keyword to get the data of REF column after getting
-- object we can access its properties with dot notation

--on the other hand object table objects values can be get with
--VALUE keyword (alternative syntax) It seems unnecessary
SELECT VALUE(otb) FROM object_table_books otb;

VALUE(OTB)

-------------------------------
HR.BOOK_TYPE(1111111,2009)
HR.BOOK_TYPE(2222222,2009)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

2 rows selected

SELECT VALUE(otb).isbn_no FROM object_table_books otb;

VALUE(OTB).ISBN_NO
----------------------
1111111
2222222                

2 rows selected