This was actually my presentation on Aug 6, 2007 in Turkcell, about "Object Datatypes and Object Views" but I will write more on this article. This article consists of three parts.
Oracle is a relational database, but it has a great feature of object-oriented abstraction. Hence, you can use Oracle as a fully object-oriented database and develop your applications on this was. Also, you can use this object-oriented features on your relational schema, without modifying the relational data, just using object views.
You can download my presentation about "Object Relational Features of Oracle" from here.
Let's start with native datatypes vs. object datatypes:
NATIVE DATATYPES
1- CHARACTER DATATYPES
The character datatypes store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme, generally called a character set or code page.
a) CHAR Datatype
The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that:
When you insert or update a row in the table, the value for the CHAR column has the fixed length.
If you give a shorter value, then the value is blank-padded to the fixed length.
If a value is too large, Oracle returns an error.
Oracle compares CHAR values using blank-padded comparison semantics.
b) VARCHAR2 and VARCHAR Datatypes
The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.
For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50.
Oracle compares VARCHAR2 values using nonpadded comparison semantics.
The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.
c) NCHAR and NVARCHAR2 Datatypes
NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set. AL16UTF16 and UTF8 are both Unicode encoding.
The NCHAR datatype stores fixed-length character strings that correspond to the national character set.
The NVARCHAR2 datatype stores variable length character strings.
When you create a table with an NCHAR or NVARCHAR2 column, the maximum size specified is always in character length semantics. Character length semantics is the default and only length semantics for NCHAR or NVARCHAR2.
For example, if national character set is UTF8, then the following statement defines the maximum byte length of 90 bytes:
CREATE TABLE tab1 (col1 NCHAR(30));
This statement creates a column with maximum character length of 30. The maximum byte length is the multiple of the maximum character length and the maximum number of bytes in each character.
NCHAR
The maximum length of an NCHAR column is 2000 bytes. It can hold up to 2000 characters. The actual data is subject to the maximum byte limit of 2000. The two size constraints must be satisfied simultaneously at run time.
NVARCHAR2
The maximum length of an NVARCHAR2 column is 4000 bytes. It can hold up to 4000 characters. The actual data is subject to the maximum byte limit of 4000. The two size constraints must be satisfied simultaneously at run time.
d) LONG Datatype
Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.
LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.
2- NUMERIC DATATYPES
a) NUMBER Datatype
The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision.
The following numbers can be stored in a NUMBER column:
• Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits
• Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits
• Zero
• Positive and negative infinity (generated only by importing from an Oracle Version 5 database)
b) BINARY_FLOAT Datatype
BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte.
c) BINARY_DOUBLE Datatype
BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.
3- DATE Datatype
The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era, or 'AD'). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.
Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
4- LOB Datatypes
The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format. They provide efficient, random, piece-wise access to the data. Oracle recommends that you always use LOB datatypes over LONG datatypes. You can perform parallel queries (but not parallel DML or DDL) on LOB columns.
LOB datatypes differ from LONG and LONG RAW datatypes in several ways. For example:
A table can contain multiple LOB columns but only one LONG column.
A table containing one or more LOB columns can be partitioned, but a table containing a LONG column cannot be partitioned.
The maximum size of a LOB is 8 terabytes, and the maximum size of a LONG is only 2 gigabytes.
LOBs support random access to data, but LONGs support only sequential access.
LOB datatypes (except NCLOB) can be attributes of a user-defined object type but LONG datatypes cannot.
Temporary LOBs that act like local variables can be used to perform transformations on LOB data. Temporary internal LOBs (BLOBs, CLOBs, and NCLOBs) are created in a temporary tablespace and are independent of tables. For LONG datatypes, however, no temporary structures are available.
Tables with LOB columns can be replicated, but tables with LONG columns cannot.
SQL statements define LOB columns in a table and LOB attributes in a user-defined object type. When defining LOBs in a table, you can explicitly specify the tablespace and storage characteristics for each LOB.
a) BLOB Datatype
The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to 8 terabytes of binary data.
BLOBs participate fully in transactions. Changes made to a BLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, BLOB locators cannot span transactions or sessions.
b) CLOB and NCLOB Datatypes
The CLOB and NCLOB datatypes store up to 8 terabytes of character data in the database. CLOBs store database character set data, and NCLOBs store Unicode national character set data. Storing varying-width LOB data in a fixed-width Unicode character set internally enables Oracle to provide efficient character-based random access on CLOBs and NCLOBs.
c) BFILE Datatype
The BFILE datatype stores unstructured binary data in operating-system files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to 8 terabytes of data.
BFILEs are read only; you cannot modify them. They support only random (not sequential) reads, and they do not participate in transactions. The underlying operating system must maintain the file integrity, security, and durability for BFILEs. The database administrator must ensure that the file exists and that Oracle processes have operating-system read permissions on the file.
5- RAW and LONG RAW Datatypes
The RAW and LONG RAW datatypes are used for data that is not to be interpreted (not converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, LONG RAW can be used to store graphics, sound, documents, or arrays of binary data. The interpretation depends on the use.
RAW is a variable-length datatype like the VARCHAR2 character datatype, except Oracle Net Services (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, Oracle Net Services and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set and the user session character set, if the two character sets are different.
When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.
LONG RAW data cannot be indexed, but RAW data can be indexed.
6- ROWID and UROWID Datatypes
Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.
A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.
A column of the UROWID datatype can store all kinds of rowids. The value of the COMPATIBLE initialization parameter (for file format compatibility) must be set to 8.1 or higher to use UROWID columns.
7- ANSI, DB2, and SQL/DS Datatypes
SQL statements that create tables and clusters can also use ANSI datatypes and datatypes from IBM's products SQL/DS and DB2. Oracle recognizes the ANSI or IBM datatype name that differs from the Oracle datatype name, records it as the name of the datatype of the column, and then stores the column's data in an Oracle datatype based on the conversions.
8- XML Datatypes
XMLType can be used like any other user-defined type. XMLType can be used as the datatype of columns in tables and views. Variables of XMLType can be used in PL/SQL stored procedures as parameters, return values, and so on. You can also use XMLType in PL/SQL, SQL and Java, and through JDBC and OCI.
A number of useful functions that operate on XML content have been provided. Many of these are provided both as SQL functions and as member functions of XMLType. For example, function extract extracts a specific node(s) from an XMLType instance. You can use XMLType in SQL queries in the same way as any other user-defined datatypes in the system.
9- URI Datatypes
A URI, or uniform resource identifier, is a generalized kind of URL. Like a URL, it can reference any document, and can reference a specific part of a document. It is more general than a URL because it has a powerful mechanism for specifying the relevant part of the document.
OBJECT DATATYPES
Introduction
Object types and other user-defined datatypes let you define datatypes that model the structure and behavior of the data in their applications. An object view is a virtual object table.
Oracle object technology is a layer of abstraction built on Oracle's relational technology. New object types can be created from any built-in database types or any previously created object types, object references, and collection types. Metadata for user-defined types is stored in a schema available to SQL, PL/SQL, Java, and other published interfaces. Object datatypes make it easier to work with complex data, such as images, audio, and video.
An object type differs from native SQL datatypes in that it is user-defined, and it specifies both the underlying persistent data (attributes) and the related behaviors (methods). Object types are abstractions of the real-world entities, for example, purchase orders. Object types store structured business data in its natural form and allow applications to retrieve it that way.
Internally, statements about objects are still basically statements about relational tables and columns, and you can continue to work with relational datatypes and store data in relational tables. But you have the option to take advantage of object-oriented features too. You can use object-oriented features while continuing to work with most of your relational data, or you can go over to an object-oriented approach entirely. For instance, you can define some object datatypes and store the objects in columns in relational tables. You can also create object views of existing relational data to represent and access this data according to an object model. Or you can store object data in object tables, where each row is an object.
Reasons to Use Object Datatypes
• In general, the object-type model is similar to the class mechanism found in C++ and Java. Like classes, objects make it easier to model complex, real-world business entities and logic, and the reusability of objects makes it possible to develop database applications faster and more efficiently. The object type is an excellent way to extend PL/SQL with new functionality in the same way a class structure does this in C++ or Java.
• By natively supporting object types in the database, Oracle enables application developers to directly access the data structures used by their applications. No mapping layer is required between client-side objects and the relational database columns and tables that contain the data.
• Object abstraction and the encapsulation of object behaviors also make applications easier to understand and maintain.
• Object types can be used to enforce standardization as well. I can create a new type, say ADDRESS_TYPE, which encapsulates the definition of an address - the discrete components that make it up. I can even add convenience functions (methods) around this type, perhaps to return the address in a format suitable for printing on labels for example. Now, whenever I create a table needing a column that is an address, I can simply declare it is as ADDRESS_TYPE. The attributes that constitute an address will be added to my table for me automatically.
• Object types can be used to present an object relational view of strictly relational data: Object Views
Objects Can Encapsulate Operations Along with Data
Database tables contain only data. Objects can include the ability to perform operations that are likely to be needed on that data. Thus a purchase order object might include a method to sum the cost of all the items purchased. Or a customer object might have methods to return the customer's buying history and payment pattern. An application can simply call the methods to retrieve the information.
Objects Are Efficient
Using object types makes for greater efficiency:
• Object types and their methods are stored with the data in the database, so they are available for any application to use. Developers can benefit from work that is already done and do not need to re-create similar structures in every application.
• You can fetch and manipulate a set of related objects as a single unit. A single request to fetch an object from the server can retrieve other objects that are connected to it. For example, when you select a customer object and get the customer's name, phone, and the multiple parts of his address in a single round-trip between the client and the server. When you reference a column of a SQL object type, you retrieve the whole object.
Objects Can Represent Part-Whole Relationships
In a relational system, it is awkward to represent complex part-whole relationships. A piston and an engine have the same status in a table for stock items. To represent pistons as parts of engines, you must create complicated schemas of multiple tables with primary key-foreign key relationships. Object types, on the other hand, give you a rich vocabulary for describing part-whole relationships. An object can have other objects as attributes, and the attribute objects can have their own object attributes too. An entire parts-list hierarchy can be built up in this way from interlocking object types.
|
Set as Homepage
Add to Favourites
Recommend
Contact
