Object Relational Features and Advanced Database Concepts

1. Department of Computer and Information Science,School of Science, IUPUI Object-Relational Features Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Object-Relational Databases • Types, Object Views, Methods • Nested Tables and Varying Arrays • Large Objects • Advanced OO Concepts 2

3. Types • Abstract datatypes groups related columns/data .create type ADDRESS_TY as object( Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER); • Abstract datatypes can be nested . • security – must have EXECUTE privilege to use a type. • set describe depth – see attributes within an object. 3

4. Types: indexing, selecting, manipulating Create type PERSON_TY ( Name VARCHAR2(25), Address ADDRESS_TY);Create table CUSTOMER ( Cust_Id NUMBER, Person PERSON_TY ); • Select:select c.Name, c.Person.Address.City from CUSTOMER c where c.Person.Address.City like ‘I%’; • Indexing:create index IDX_CUST on CUSTOMER(Person.Address.City); • Insert:insert into CUSTOMER values (1, PERSON_TY(‘xxxx’, ADDRESS_TY(‘aaaa’, ‘bbb’, ccc’))); 4

5. Object Views create table CUSTOMER(Cust_id NUMBER PRIMARY KEY, Name VARCHAR2(25), Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER); create type ADDRESS_TY as object( Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER);create type PERSON_TY ( Name VARCHAR2(25), Address ADDRESS_TY);create or replace view CUSTOMER_OV(Cust_id, person) asselect Cust_id, PERSON_TY (Name, ADDRESS_TY(street, city, state, zip))from CUSTOMER; • Inserting into original table: • insert into CUSTOMER values ( 123, ‘name’, ‘Street’, ‘City’, ‘ST’, 99999 ); • Inserting into object view:insert into CUSTOMER_OV values ( 123, PERSON_TY(‘name’, ADDRESS_TY( ‘Street’, ‘City’, ‘ST’, 99999 ))); 5

6. INSTEAD OF Triggers • Views usually cannot use standard table-based PL/SQL Triggers, but they can use INSTEAD OF triggers. Works with either normal views or object views. create trigger AUTHOR_PUBLISHER_UPDATEinstead of UPDATE on AUTHOR_PUBLISHERfor each rowbegin …end; 6

7. Methods • Methods allow types to have “behavior”. • Methods are declared inside CREATE TYPE, they are defined with CREATE TYPE BODY. create type PERSON_TY ( Name VARCHAR2(25), Address ADDRESS_TY, Birthday DATE, member function AGE (BirthDate IN DATE) return NUMBER);create or replace type body PERSON_TY as member function AGE (Birthdate DATE) return NUMBER is begin return ROUND(sysdate – Birthdate); end; end; 7

8. Collections: Varying Arrays • Definition – a varying array is a set of objects, each of same type, seen as a column within a table. The size is limited when created. • Creating a Varying Array • Create type TOOL_TY as object (…); • Create type TOOLS_VA as varray(5) of VARCHAR2(25); • Create table TOOL_BORROWER (…, Tools TOOLS_VA); • Describing – requires queries on data dictionary to show varray structure. • Selecting – requires PL/SQL, see p. 571-572 • Inserting – insert into TOOL_BORROWER values (‘name’, TOOLS_VA(‘tool1’, ‘tool2’, ‘tool3’)); • Storage – stored in-line, ie. within table data segment. 8

9. Collections: Nested Tables • Definition – a nested table is a table within a table. Their size is not limited as with varying arrays. • Creating a Nested Table • Create type TOOL_TY as object (…); • Create type TOOLS_NT as table of TOOL_TY; • Create table TOOL_BORROWER (…, nested table Tools TOOLS_NT); • Describing – set describe depth will allow the describe command to show nested tables. • Selecting – requires the TABLE() function (previous to 9i use THE() function).This “flatten’s” or de-normalizes the nested table’s relationship.Select BreederName, N.Name, N.birthdate from BREEDER, TABLE(breeder.Animals) N • Inserting – insert into TABLE(select Animals from BREEDER where Breedername = ‘x’) values (ANIMAL_TY (‘x’,’y’,’z’)); • Storage – Nested tables can be stored in or out-of-line. Recommend they be stored out-of-line with a storage clause to improves query performance. 9

10. Large Object Datatypes • BLOB – binary field up to 4Gb. • CLOB – character field up to 4Gb. • NCLOB – CLOB that supports MBCS (multi-byte character set). • BFILE – read-only binary data stored in a file outside of the database. 10

11. LOB Storage, Initialization • LOB Storage – BLOB and (N)CLOB columns are stored out-of-line. They require the CREATE TABLE statement include a LOB STORAGE clause to tell where the LOB segment is stored. BFILE columns are by their nature stored out-of-line. • LOBs use at least 1 entire data block per row in the LOB segment. Normal DATA segments store multiple rows per block (up to 100% -PCTFREE). • Initializing LOBs – use EMPTY_BLOB or EMPTY_CLOB instead of NULL to initialize a LOB. • NULL will create a LOB with no data. • EMPTY_BLOB/CLOB will create an empty “LOB locater” (pointer) without allocating the empty block. 11

12. DBMS_LOB • LOB data is manipulated with the DBMS_LOB package. • For all LOBs: • APPEND • COMPARE • COPY • ERASE • GETCHUNKSIZE • GETLENGTH • INSTR • ISOPEN • OPEN • READ • SUBSTR • TRIM • WRITE • WRITEAPPEND • BFILE specific functions: • FILEOPEN • FILECLOSE • FILECLOSEALL • FILEEXISTS • FILEGETNAME • FILEISOPEN • LOADFROM FILE • Temporary LOB functions: • CREATETEMPORARY • FREETEMPORARY • ISTEMPORARY 12

13. BFILE • Read-only binary data stored in a file outside of the database. • CREATE DIRECTORY PROPOSAL_DIR AS ‘C:\My Documents’; • Insert into thetable ( col1, bfilecol ) values ( ‘value1’, BFILENAME(‘proposal_dir’, ‘myfile.doc’); • Deleting LOBs – internal LOBs when deleted have both their data and LOB locater deleted. Deleting a BFILE only deletes the locater, the file is read only and thus not changed. 13

14. row objects vs. column objects • Row objects • Column objects 14

15. REF, DEREF, VALUE • REF • DEREF • VALUE 15

16. Acknowledgements • Loney, Oracle Database 10g The Complete Reference