UTL_FILE A Great Utility For PL/SQL

UTL_FILE  A Great Utility For PL/SQL 

 By Anju Ravindran  

UTL_FILE is a package that has been welcomed warmly by PL/SQL developers. It allows PL/SQL programs to both read from and write to any operating system files that are accessible from the server on which your database instance is running.

Getting Started with UTL_FILE

The UTL_FILE package is created when the Oracle database is installed. The utlfile.sql script contains the source code for this package’s specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym UTL_FILE for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

UTL_FILE programs

Following table shows the UTL_FILE program names and descriptions.

UTL_FILE Programs
Name	Description	Use in SQL
FCLOSE	Closes the specified files	No
FCLOSE_ALL	Closes all open files	No
FFLUSH	Flushes all the data from the UTL_FILE buffer	No
FOPEN	Opens the specified file	No
GET_LINE	Gets the next line from the file	No
IS_OPEN	Returns TRUE if the file is already open	No
NEW_LINE	Inserts a newline mark in the file at the end of the current line	No
PUT	Puts text into the buffer	No
PUT_LINE	Puts a line of text into the file	No
PUTF	Puts formatted text into the buffer	No

Before writing programs

·          You need to modify the INIT.ORA parameter initialization file of your instance.
·         You will have to contact your database administrator and have him or her make the changes (if willing) and then “bounce” the database.
·         You need to get the format of the parameter entries correct. Here’s the format of the parameter for file access in the INIT.ORA file:  utl_file_dir = <directory>
·         You need to add exception sections to your programs.

 UTL_FILE nonprogram elements

When you open a file, PL/SQL returns a handle to that file for use within your program. This handle has a datatype of UTL_FILE.FILE_TYPE currently defined as the following:

TYPE UTL_FILE.FILE_TYPE IS RECORD (id BINARY_INTEGER);

As you can see, UTL_FILE.FILE_TYPE is actually a PL/SQL record whose fields contain all the information about the file needed by UTL_FILE. However, this information is for use only by the UTL_FILE package. You will reference the handle, but not any of the individual fields of the handle. (The fields of this record may expand over time as UTL_FILE becomes more sophisticated.)Here is an example of how to declare a local file handle based on this type:

DECLARE

   file_handle UTL_FILE.FILE_TYPE;

BEGIN

   ...

The UTL_FILE process flow

The following sections describe each of the UTL_FILE programs, following the process flow for working with files. That flow is described for both writing and reading files. In order to write to a file you will (in most cases) perform the following steps:

1.      Declare a file handle. This handle serves as a pointer to the file for subsequent calls to programs in the UTL_FILE package to manipulate the contents of this file.
2.      Open the file with a call to FOPEN, which returns a file handle to the file. You can open a file to read, replace, or append text.
3.      Write data to the file using the PUT, PUTF, or PUT_LINE procedures.
4.      Close the file with a call to FCLOSE. This releases resources associated with the file.

More links       

   http://www.unix.org.ua/orelly/oracle/bipack/ch06_02.htm     

   http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm