Fast Track

FAST TRACK

Managing Unstructured Data with Oracle8

By Steve Bobowski

Oracle 8 provides a variety of new datatypes you can use to create database applications that take advantage of text documents, video clips, audio files, and other types of multimedia data.

Most database applications written today must be able to manage collections of unstructured data. Oracle8 has several new datatypes you can use to provide the foundation for developing content-rich database applications that store and manage access to text documents, static images, video clips, audio files, and other types of multimedia data. For example, you could use these features to manage a library of important documents in an Oracle database or to store an employee's photo and voice pattern in a corporate database to support a security application.

Oracle8's new large object datatypes allow you to create such applications. In particular, the BFILE datatype allows you to manage database access to large binary objects stored in operating system files.

New Datatypes in Oracle8

Oracle8 has several large object (LOB) datatypes to support applications that must manage large unstructured objects. For example, a CLOB column stores character objects, such as text documents, while a BLOB column stores binary objects such as graphics, video clips, or sound files.

You can use both the CLOB and BLOB datatypes to declare table columns that store LOB data within the database. In other words, the Oracle database stores CLOBs and BLOBs within a tablespace in the database itself. The advantage of this method is that the database protects the data, using the database server mechanisms that protect all other types of table data, such as backup-and-recovery and security mechanisms.

But there are also many important issues to consider when you decide to store LOBs by using CLOBs and BLOBs. The L in LOB stands for large, which typically translates to significant storage requirements. Therefore, if you plan to store many CLOBs and BLOBs inside your database, you need to be sure you dedicate enough storage resources so that the database can manage all the data. In addition, there are many application-dependent issues to consider with respect to tuning LOBs, including data caching and transaction logging as well as several space-management issues.

In addition to the LOB datatypes, Oracle8 also provides another new datatype: BFILE. A BFILE column stores LOB locators, which are small file pointers to LOBs that the host server's file system manages outside the database itself. The BFILE datatype is appropriate for relatively static LOB data that can be managed better by a file system, such as files on a read-only CD-ROM. For example, a BFILE column might consist of a list of filename references for photos or sound files that are stored on a CD-ROM. Figure 1 illustrates the general principle of the BFILE datatype.

Because BFILE data is not managed internally by the Oracle database, changes to BFILE data are not protected by transactions. Additionally, changes made to BFILE data are not protected by standard database backup-and-recovery mechanisms.

The remainder of this article shows you how to get started using the BFILE datatype to safely manage LOB data that is stored externally to the database in cooperation with the host server's file system.

Creating Tables with BFILEs

A table in an Oracle database can contain one or more BFILE columns (that is, columns declared with the BFILE datatype). Simply indicate the BFILE datatype for a column in your table in the last line of a code segment, such as in the following example:

CREATE TABLE employee( 
  empno INTEGER PRIMARY KEY, 
  ename VARCHAR2(10), 
  job VARCHAR2(20), 
  mgr INTEGER,
  hiredate DATE,, 
  sal NUMBER(7,2), 
  comm NUMBER(7,2), 
  job deptno INTEGER, 
  photo BFILE);

Directory Management

After creating a table or tables with BFILE columns, the next step is to declare the file system directory (or directories) that contain the LOBs referenced by the BFILE columns in database tables.

To declare directory objects in an Oracle database, use Oracle8's SQL command CREATE DIRECTORY. For example, the following statement registers a file system path on a Microsoft Windows NT server:

CREATE DIRECTORY emp_photos AS 'e:\oracle8\bfiles\emp_photos';

The name of a directory object in an Oracle database is simply a logical name (for example, EMP_PHOTOS) and does not have to match the corresponding file system path (for example, e:\oracle8\bfiles\emp_photos). Keep in mind that the actual file system path is case-sensitive for operating systems that make such distinctions, however.

It is important to plan ahead before you attempt to declare directories in an Oracle database. Consider the following guidelines before you start managing file system directories for BFILEs:

You must have the CREATE ANY DIRECTORY system privilege to register a file system directory in an Oracle database. As with most system privileges, only database administrators or application administrators should have this powerful privilege. You should actively protect the integrity of the database system by limiting the number of people who can perform these actions.
After you declare a directory, make sure that it actually exists in the file system. Because the Oracle database does not manage this dependency, it is up to the database administrator to manage this file system task.
Make sure that the Oracle database server processes have the necessary operating-system privileges to access the file system directory you declare.
Store LOBs in directories that are separate from other types of database information. For example, do not store BFILE LOBs in the same directory that holds a database's data files, transaction log files, or control files--doing so could open security holes or lead to space management problems.

Security Configuration

Configure directories for BFILE columns in an Oracle database so that the database server itself can manage access to the data in the server's file system in a secure manner. To control which users and applications have access privileges for a declared directory, grant the READ object privilege for each directory. For example, the following GRANT statement gives the employee management application's role read access to the EMP_PHOTOS directory:

GRANT READ ON DIRECTORY emp_photos TO employee_management;

Therefore, when a user starts the employee management application, the application enables the EMPLOYEE_ MANAGEMENT role so that the user can read files from the file system directory, which is pointed to by the logical PHOTOS directory in the database.

Before the BFILE

Prior to Oracle8 universal data server, Oracle databases simulated the functionality of the new BFILE datatype by writing special database programs that read a character string "file pointer" stored in a basic CHAR or VARCHAR2 column. There are several inherent problems with this design strategy, however. For one, you have to embed a pathname in the specification of each file pointer--consequently, if you have to move your data, all the file pointers have to be updated as well, which might not be easy to do, depending on your design.
Additionally, security of data accessed outside the database cannot be managed within the database itself. It must be managed by the host server's operating system, which adds an extra level of complexity to security administration. And poorly designed security policies could lead to unauthorized access to database information.

Application Development Issues

The previous sections of this article show you how to address the administration and configuration issues that relate to using the BFILE datatype. Now let's focus on how applications work with BFILE columns and the data pointed to by the LOB locators in a BFILE column.

DML Operations. All DML operations involving BFILEs simply manage the locators to the target LOB files external to the database. The following INSERT statement adds a new employee and points to a graphic file of the employee's photograph:

INSERT INTO employee
  VALUES (112, 'GSMITH',
  'SALES PERSON', 14,
  SYSDATE, 1000, 200, 2,
  BFILENAME('EMP_PHOTOS', 'emp_gsmith.gif'));

Note the use of the SQL BFILENAME function in the example above. The BFILENAME function takes two parameters: a directory object and a filename in the corresponding file system directory. When a SQL statement calls the BFILENAME function, the Oracle database does not check the validity of either the directory or the filename parameters, which means that you can specify anything for either parameter.

Furthermore, the Oracle database does not manage the dependency of specified BFILE locators and the corresponding files in the server's file system. It is the application's responsibility to make sure these files actually exist in the specified location before the application performs subsequent PL/SQL or Open Call Interface (OCI) operations that involve the LOBs pointed to by the BFILE locators.

An update to a BFILE locator simply changes the value of the locator, not the actual data in the external file. The following UPDATE statement changes the locator for the photo in an employee record:

UPDATE employee
SET photo = BFILENAME('EMP_PHOTOS', 'emp_112.gif') WHERE empno = 112;

Working with External File Data. An Oracle database server does not let you actually manipulate the bytes of LOB data stored externally to the database. By definition, the files pointed to within a BFILE column are read-only in the context of database applications. If you want to update the data in a LOB stored in the file system, you must do so with an application that manipulates the files directly via the operating system.

Oracle8 does come with a new PL/SQL utility package, called DBMS_LOB, which applications can use to manipulate the data in LOBs of all types. Several of the procedures and functions in this utility package are appropriate for use with BFILE LOBs. For example, an application can use the FILEOPEN procedure to open a BFILE LOB for read-only access, use the READ procedure to read it, and then use the FILECLOSE procedure to close it. The following PL/SQL procedure demonstrates the use of these new procedures:

CREATE OR REPLACE PROCEDURE read_emp_photo
  (empid IN INTEGER) IS
  loc BFILE;
  buffer RAW(32767);
  amount BINARY_INTEGER := 32767;
  position INTEGER := 2147483647; BEGIN
  SELECT photo INTO loc FROM employee WHERE empno = empid;
  DBMS_LOB.FILEOPEN(loc, DBMS_LOB.FILE_READONLY)
  LOOP
   DBMS_LOB.READ(loc,amount, position, buffer);
   position := position + amount;
  END LOOP;
  DBMS_LOB.FILECLOSE(loc);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
   BEGIN
    RAISE_APPLICATION_ERROR (-20000, 'Invalid employee number');
   END;
END;

Of course, an application that uses the DBMS_LOB package to read BFILE LOBs must be able to actually manipulate the bytes of the LOB when they are read and then do something with them. Executing the above procedure with a tool such as SQL*Plus does nothing more than return a "Statement processed" message.

Other Considerations

There are a couple of other issues that are important to consider before you start developing applications that use BFILE LOBs. For one, the Oracle database limits the number of concurrent BFILE LOBs that a user session can have open. This limit is set by the server parameter SESSION_MAX_OPEN_FILES.

According to the Oracle documentation for Oracle8 Release 8.0.3, the default setting for this server parameter is 10. However, you should double-check the parameter setting to make sure that it is not set to 0. If the setting for SESSION_MAX_OPEN_FILES on your server is too low, you can adjust it via the server's parameter file and then restart the server to have it take effect.

Finally, when using the procedures and functions of the DBMS_LOB utility package with BFILE LOBs, make sure that programs that open files also close them before exiting. For example, in the procedure above, notice the call to the FILECLOSE procedure. The DBMS_LOB package also includes a FILECLOSEALL procedure for closing all open BFILE LOBs of a session, using a single call.

Steve Bobrowski is the CEO of OraWorld (www.oraworld.com), an information hub on the Web dedicated to teaching Oracle professionals how to get the most out of Oracle products. Bobrowski is the author of the award-winning Mastering Oracle7 & Client/Server Computing (Sybex, 1996) and the new book Oracle8 Architecture (Oracle Press, 1997).

This is a copy of an article published @ http://www.oramag.com/