Utl_File

Overview of enhancements introduced in
  Oracle9i Database Version 9.2.0

Pre 9.2.0, the way to denote the director(ies) for files was via the UTL_FILE_DIR initialization parameter. This suffered from the disadvantages that the instance had to be bounced to make changes to the list of directories and that there was no secutity scheme (all users could access files on all diretories). Version 9.2.0 allows the same mechanism (the DIRECTORY schema object) to be used with Utl_File as is used for BFILEs. You should consider the UTL_FILE_DIR initialization parameter as slated for deprecation.

The line length limit for Utl_File.Get_Line and Utl_File.Put_Line has been increased from 1K to 32K.

The following new API primitives have been introduced...

For operating system file management

• procedure Fgetattr
• procedure Fcopy
• procedure Fremove
• procedure Frename

For handling RAW data

• procedure Fseek
• function  Fgetpos
• procedure Get_Raw
• procedure Put_Raw

In additon, Get_Line and Get_Line_Nchar have acquired a new defaulted binary_integer parameter len. And Put_Line has acquired a new defaulted boolean parameter autoflush.

Utl_File performance is improved via transparent internal reimplementation.

To explore some of the possible exceptions in the following tests, you'll need to log on to the datatbase machine as the Oracle user and create and delete files.

Create the UTL_FILE_TEST DIRECTORY
and the PROGRAMMER test user

These samples uniformly use the new-in-9.2.0 approach for the location formal parameter by providing the name of a directory as the actual parameter. We'll use the same filesystem directory that's designated for user dump files. And for convenience, we'll let the test user query the v$parameter view and execute Dbms_Pipe. Create the user.

API

The package exposes the following prcedures and functions. None is overloaded.

Opening, writing and closing

• function  Fopen
• function  Is_Open
• procedure Put_Line
• procedure Fclose
• procedure Fclose_All

This sample illustrates all of the above APIs. This illustrates the new 9.2.0 functionality to open using a directory schema object.

Mimicing Unix  ls, cp, mv, rm

• procedure Fgetattr
• procedure Fcopy
• procedure Frename
• procedure Fremove

This sample illustrates all of the above APIs, all of which are new in 9.2.0.

Handling line-oriented character data

This sample demonstrates autoflush by writing a line to file and waiting on a message via Dbms_Pipe from another session to give you time to observe with tail -f  (on Unix, or the equivalent on other operating systems). Create the Wait package. Otherwise, this sample shows only pre-9.2.0 functionality.

• procedure Get_Line
• procedure Put
• procedure Putf
• procedure Put_Line
• procedure New_Line
• procedure Fflush

This sample illustrates all of the above APIs. and illustrates the new len parameter to Get_Line and the new autoflush parameterto Put_Line.

Handling line-oriented NCHAR data

Each procedure (or function) for NCHAR is identical in shape and meaning to its counterpart for VARCHAR2 with the exception that Put_Line_Nchar doesn't have an autoflush parameter. Thus no code samples are provided.

• function  Fopen_Nchar
• procedure Get_Line_Nchar
• procedure Put_Nchar
• procedure Putf_Nchar
• procedure Put_Line_Nchar

Handling RAW data

• procedure Put_Raw
• procedure Get_Raw
• function  Fgetpos
• procedure Fseek

This sample illustrates Get_Raw. This sample illustrates Put_Raw. This sample illustrates Fseek.

Note: Port-specific Bug #2546782, raised against 9.2.0 on Windows 2000, reports wrong output from Utl_File.Put_Raw.

Exceptions

• file_open
• charsetmismatch
• invalid_path
• invalid_mode
• invalid_filehandle
• invalid_operation
• read_error
• write_error
• internal_error
• invalid_maxlinesize
• invalid_filename
• access_denied
• invalid_offset
• delete_failed
• rename_failed

Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 10-June-2002