ORACLE’s Object Tables


You know, I have defined a new Oracle Data Type called book_type in my Database. Please refer it to by the following URL.

https://tunatore.wordpress.com/2011/04/29/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 I would like to create an object table contaning many book_types inside of it
–object tables can only store types inside it

CREATE TABLE object_table_books OF book_type;

–However we can reference an object table in our standard table definition 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 realitionship
1 rows inserted.

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

BORROW_ID              BORROWER                    DEREF(BOOK).ISBN_NO
———————- ————————— ———————-
1                                     tunatore                           1111111                

1 rows selected

–while selecting the data from a standard table containing a REF column data
— I should use DEREF keyword to get the data of REF column after getting
— the object moreover I can access its properties with dot notation

–on the other hand object table objects values can be get with
–VALUE keyword (alternative syntax)

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s