Bonus
Day

Day 23

Oracle8: New Features

On June 24, 1997, Oracle Corporation formally announced the next major release of the Oracle RDBMS: Oracle8. This new release is one of the major components of Oracle's Network Computing Architecture. Oracle Corporation is emphasizing several new characteristics of the Oracle8 server, such as:

• Improved scalability: The maximum size of an Oracle database has been increased to support hundreds of terabytes, depending on the operating system on which it resides.
• Improved security administration: As an example, the Oracle8 server now includes password management so that a password has a limited lifetime, and must meet certain complexity criteria, such as minimum length. An account can be locked after a specified number of failed login attempts.
• Improved performance via partitioning: A table or index can be divided into smaller pieces, called partitions, based on the value of one or more columns. A table's partitions can be individually managed so that operations in one partition do not affect the availability of data in the other partitions. Also, insert, update, and delete operations against partitioned tables can be processed in parallel; in other words, the

Oracle8 server can assign a portion of the work to execute a single DML statement to multiple processes, which may then be allocated to multiple processors by the server operating system. As a result, the parallel DML operation is completed more quickly.
• Enhanced support for database replication: The performance and manageability of database replication has been significantly improved.
• Capability to handle a much larger number of concurrent users: By pooling database connections, the Oracle8 server is able to service a much larger number of concurrent users—up to 3,000, depending on the server operating system and server hardware resources.
• New and improved datatypes: Some existing datatypes have been enhanced, and new datatypes have been introduced.
• Improved SELECT statement: A new feature of the SELECT statement allows a subquery to be used in place of a table in a FROM clause.
• Object-relational features via the Oracle Objects Option: A new RDBMS option supports the definition and use of new datatypes.

Many of these new features are of primary interest to database administrators and system architects. However, this lesson will focus on new features of Oracle8 that are of interest to database designers and application developers. The material in this lesson is based on Oracle8 for Windows NT, version 8.0.2.0.1 Beta2.

Enhancement of Existing Datatypes and
Some New Datatypes

Oracle8 has increased the maximum size of some existing Oracle datatypes:

• The capacity of a RAW column has been increased from 255 bytes to 2,000 bytes.
• The capacity of a VARCHAR2 column has been increased from 2,000 characters to 4,000 characters.

In addition, a single table can now have up to 1,000 columns.

Two new datatypes, NCHAR and NCHAR VARYING, can be used with Oracle8. These datatypes can be used for national character sets that require more than one byte per character; this is needed for many Asian languages.

Large Objects, or LOBs

Oracle8 provides much more support in the area of large objects. In fact, a new category of datatypes, called LOB, or Large Object, is introduced with Oracle8.

LOBs can be one of four datatypes:

• BLOBs, which store unstructured binary data
• CLOBs, which store character data
• NCLOBs, which store character data using a multibyte national character set (primarily designed to support Asian languages)
• BFILEs, which reference binary files in the filesystem

There are several advantages to using LOBs. First, a LOB can accommodate 4GB or twice the capacity of the LONG or LONG RAW column. Second, a table can contain more than one LOB column, whereas it can contain only a single LONG or LONG RAW column. Also, the data stored in a LOB column is kept in a storage area separate from the table that contains the LOB column, resulting in better overall performance. Let's look at some examples of how these datatypes can be used.

Using BLOBs

You can think of the BLOB datatype as a new, improved version of the LONG RAW datatype. A BLOB column can store twice as much as a LONG RAW column. Unlike a LONG RAW column, an INSERT statement with a subquery may contain a BLOB column. Listing 23.1 is an example of the creation of a table that contains a BLOB column. The BLOB column is used to store a scanned image of a potential instructor's resume.

Listing 23.1. Creating a table that contains a BLOB column.

SQL> create table Instructor_Application (
  2  Application_Number number,
  3  Last_Name          varchar2(30),

                                                           continues

Listing 23.1. continued

  4  First_Name         varchar2(30),
  5  MI                 varchar2(1),
  6  Resume             blob,
  7  Status             varchar2(30));

Table created.

Using CLOBs

The CLOB datatype is similar to the LONG datatype. Like the LONG datatype, the CLOB datatype is used for storing long strings. However, the CLOB datatype has twice the capacity of the LONG datatype—4GB instead of 2GB.

In Listing 23.2, you can see an example of how a CLOB column, named OCR_Resume, is added to the Instructor_Application table. The purpose of the OCR_Resume column is to store the text recognized by the scanning software after the resume has been scanned.

Listing 23.2. Altering a table to add a CLOB column.

SQL> alter table Instructor_Application add
  2  (OCR_Resume  clob);

Table altered.

SQL> update Instructor_Application
  2  set OCR_Resume = `Resume of A. Einstein - Born in Ulm, Germany in 1879'
  3  ;

1 row updated.

SQL> select OCR_Resume from Instructor_Application;

OCR_RESUME
--------------------------------------------------------------------------------
Resume of A. Einstein - Born in Ulm, Germany in 1879

ANALISIS
After the CLOB column has been added to the table, an UPDATE statement is used to set the value of OCR_Resume. A SELECT statement illustrates that the contents of OCR_Resume can be retrieved from the table.

Another important distinction between the CLOB and LONG datatypes is that there are fewer restrictions on referencing a CLOB column in a SQL statement. Refer to Listing 23.3 to see an example.