Listing 23.3. Inserting a row into a table with a LONG column.

SQL> create table New_Instructor_Application
  2  as
  3  select * from Instructor_Application;

Table created.

SQL> create table Old_Instructor_Application (
  2  Application_Number number,
  3  Last_Name          varchar2(30),
  4  First_Name         varchar2(30),
  5  MI                 varchar2(1),
  6  Resume             long,
  7  Status             varchar2(30));

Table created.

SQL> create table Another_Instructor_Application
  2  as
  3  select * from Old_Instructor_Application;
select * from Old_Instructor_Application
       *
ERROR at line 3:

ORA-00997: illegal use of LONG datatype

ANALYSIS
As you can see in Listing 23.3, a table named New_Instructor_Application is created with a subquery that retrieves all the columns in Instructor_Application, including OCR_Resume. The next statement creates a table named Old_Instructor_Application, which contains a column named Resume that is a LONG datatype. If you try to create another table named Another_Instructor_Application via a subquery of Old_Instructor_Application, Oracle rejects the statement.

Specifying a NULL for a BLOB or CLOB

There are two new built-in functions that are used to assign a NULL to a LOB: EMPTY_BLOB and EMPTY_CLOB. There are no arguments for either function. Listing 23.4 illustrates the use of EMPTY_BLOB in an INSERT statement.

Listing 23.4. Inserting a row into a table with a BLOB column.

SQL> insert into Instructor_Application
  2  (Application_Number, Last_Name, First_Name, MI, Resume, Status)
  3  values
  4  (1001, `EINSTEIN', `ALBERT', NULL, EMPTY_BLOB(), `REJECTED');

1 row created.

Using BFILEs

A BFILE column is used to reference a binary file that is stored in the filesystem where the Oracle8 server resides; that is, the binary file is external to the Oracle8 database. As an example, Listing 23.5 demonstrates how to create a table that contains a BFILE column.

Listing 23.5. Defining and using a BFILE column.

SQL> create table Instructor_Photo
  2  (Instructor_ID  varchar2(5),
  3   Recent_Photo   bfile);

Table created.

SQL> insert into Instructor_Photo
  2  (Instructor_ID, Recent_Photo)
  3  values
  4  (`E251', BFILENAME (`C:\Flugle\Photos', `E251.jpg'));

1 row created.

ANALYSIS
The Instructor_Photo table is created with a BFILE column named Recent_Photo. The purpose of this column is to identify the location and name of the .JPG file that contains a photograph of the instructor identified by Instructor_ID. A row is inserted into the Instructor_Photo table. As you can see, the BFILENAME function is used to supply a literal value for the BFILE column; the directory and filename are enclosed in single quotes. The contents of the binary file referenced by a BFILE column cannot be modified by a SQL statement; however, the directory and filename as contained in the BFILE column can be modified.

The CREATE DIRECTORY Statement

A new SQL statement introduced with Oracle8 is CREATE DIRECTORY. This statement is designed to work with BFILE columns. The purpose of CREATE DIRECTORY is to create an alias for a filesystem directory. Once the directory alias is defined, the privilege to read the contents of a file in that directory can be granted to a role or user.

Here's the syntax for the CREATE DIRECTORY statement:

CREATE DIRECTORY directory_alias  as `directory';

The variables are defined as follows:

directory_alias is the name that Oracle users will use to reference the directory.

directory is the fully qualified directory where the binary files that are referenced by a BFILE column reside.

For example, Listing 23.6 illustrates how a directory is created. The privilege to read files from that directory can then be granted to a database role.

Listing 23.6. Creating a directory.

SQL> create directory instructor_photos as `C:\Flugle\Photos';

Directory created.

In addition to the CREATE DIRECTORY statement, another new statement—DROP DIRECTORY—is used to drop a directory alias so that no privileges can be granted to read a directory from an Oracle8 application.

New Datatypes Available with the Oracle
Objects Option

The Oracle Objects Option is an optional component of the Oracle8 server. You must have this option to be able to use these datatypes:

• The object datatype
• Nested tables
• VARRAY
• REF

We'll be examining the object datatype, nested tables, and the VARRAY datatype in this section. To begin with, let's look at the object datatype.

The Object Datatype

Oracle8 Objects Option provides a new statement, CREATE TYPE, which can be used to create a new datatype that can then be used when creating a table or another datatype. For example, if you wanted to create a table which described the equipment that an instructor might want to use in a classroom, you could create a new datatype called Classroom_Equipment for this purpose. Once the datatype is created, you can then create a table that uses the datatype (see Listing 23.7).

Listing 23.7. Creating a new datatype.

SQL> create type Available_Room as object
  2  (building   varchar2(20),
  3   room       varchar2(6));

Type created.

Once you define a new datatype, you can specify the datatype for a column as demonstrated in Listing 23.8.

Listing 23.8. Creating a table that contains an object datatype.

SQLWKS> create table Seminar (
     2> Seminar_ID    varchar2(6),
     3> Instructor_ID varchar2(6),
     4> Location      Available_Room);
Statement processed.

An object consists of attributes (the columns defined for it) and methods (the functions and procedures that manipulate the object and return information about it). By default, Oracle creates a constructor method for each object type. This constructor method creates an instance of the object. When you want to specify a value for an object column, you must use this constructor method, which consists of the name of the object type followed by the attribute values enclosed in parentheses. Listing 23.9 provides an example.

Listing 23.9. Using a constructor method to specify a value for an object datatype.

SQLWKS> insert into Seminar
     2> (Seminar_ID, Instructor_ID, Location)
     3> values
     4> (`1001', `A101', Available_Room(`NARROW HALL', `B200'));
1 row processed.

Once you've created a table that contains an object column, you can use a SELECT statement to retrieve the values from the object column. However, you must specify each object attribute that you want to retrieve (see Listing 23.10).