The DBMS_Utility Package
Oracle Tips by
There is one especially useful function in the DBMS_Utility
package that merits attention. This is the Get_Time() function,
which returns the current time in hundredths of seconds. If you use
this function to implement time-sensitive code, make sure you are
prepared for the value to roll over to 0.
The UTL_File package, implemented with Oracle 7.3, provides
server-side file I/O to text files. There are several basic steps
required to do file I/O with the package:
1. Open one or
more files for reading or writing using the FOpen() function.
2. Read data
from a file using the Get_Line() procedure or write data
to a file using the Put_Line(), PutF(), Put(),
and/or New_Line() procedures.
3. Close files
using the FClose() or FClose_All() procedures.
The package contains the following 10 procedures and functions:
Each of these procedures and functions is described in detail in
the following sections. There is also a brief example of using the
The FClose() Procedure
The FClose() procedure is provided to close a text file
after you have finished reading data. The procedure definition is as
PROCEDURE FClose (file_handle IN OUT FILE_TYPE)
The file_handle parameter is a pointer to an open file. This
parameter is of type FILE_TYPE, a user-defined datatype in the
package spec for the UTL_File package.
The FClose_All() Procedure
The FClose_All() procedure is used to close all files that
are currently open. This is commonly used for cleaning up during error
handling or shutdown operations. The procedure accepts no parameters.
All changes to a file are flushed from the buffer before the file
The FFlush() Procedure
The FFlush() procedure is used to force all buffered changes
to files to be written to the files immediately. The procedure
definition is as follows:
PROCEDURE FFlush (file_handle IN FILE_TYPE)
The FOpen() Function
The FOpen() function is used to open a text file so that
data can be read from or written to the file. The function definition
is as follows:
FUNCTION FOpen (location IN varchar2,
filename IN varchar2,
open_mode IN varchar2)
The location parameter identifies the directory where the
file is located or where the file is to be created. The values for
this parameter are constrained by an init.ora parameter. Check with
your DBA to find out precisely which directories you can use with this
The open_mode parameter determines how the package will
handle contents of an existing file. The values for this parameter are
shown in Table 9.6.
Table 9.6 Values for the open_mode parameter of the
FOpen( ) function.
Append text to an existing file.
Read text from an existing file.
Write text to a new file, or write over an
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.