Wednesday, July 22, 2009

UTL_FILE for processing text files in oracle DB 9i/10g

UTL_FILE is one of the oracle supplied packages that can be used to write and read text files. PL/SQL programs can be constructed using the function and procedures available in UTL_FILE package to write into or read any information in the text files.

For example we can use UTL_FILE package to log the encountered errors and the activities going on with the database objects, into a text file and read the same when required. This article describes the way to process the Alert.log file located on the Database server machine using UTL_FILE package.

 

Scope

·         Oracle 9i, 10g

·         The text files to be processed have to be in the file system of Database server machine.

·         UTL_FILE package is used to do the text processing, read and write operation, on the text file.

·         Alert.log file (text file present in oracle->admin->“DB_NAME”->bdump directory) which usually records the messages and errors arising off the day to day activities on data base is used for text processing example.

·         This code in this article is worked on oracle 9i on the Windows XP machine and will run well with any higher version of oracle and any operating system by using proper file system denotations.

 

Some information on Alert.log file

·         Oracle maintains the alert.log file, and its location can be known by the following command

show parameter BACKGROUND_DUMP_DEST

·         The file is named as “alert_<DB Name>.log”

·         File contains log of the errors occurred and activities performed with the database

 

Steps to perform basic file operation oracle PL/SQL using UTL_FILE package.

1.    Creation of physical folder to contain files:

The file which is to be accessed has to be present in any directory of the file system.

Here we are trying to access the log file- “alert_oracl” present in the following path on a Windows XP machine.

D:\oracle\admin\oracl\bdump

 

We can work on any a directory in the file system that can contain some text files.

 

2.    Declaring the required parameters in the oracle parameter file:

We need to declare and initialize UTL_FILE_DIR in the “spfile” file of oracle to let the system recognize the directory which will be holding the text file. In our case, write the following on SQL prompt.

ALTER SYSTEM SET UTL_FILE_DIR='D:\oracle\admin\oracl\bdump' SCOPE=SPFILE

After using the above alter statement, restart the oracle database service on the system.

To check whether the changes have taken place, use the following query on SQL prompt

SHOW PARAMETER utl

 

 

3.    Logically mapping the physical directory with the directory entry in oracle:

 

To create a directory entry in oracle for the directory present in the file system,

Write the following on SQL prompt

 

CREATE OR REPLACE DIRECTORY utl_dir AS 'D:\oracle\admin\oracl\bdump';

 

 

4.    Check the mapping of the physical directory in oracle:

To confirm the “UTL_DIR directory entry” created in the oracle, run the following query

SELECT * FROM ALL_DIRECTORIES;

 

5.    Steps to write into a file:

Write the following block in SQL prompt to create and to write into a new file.

DECLARE

v_file_handle UTL_FILE.FILE_TYPE;

 

BEGIN

 

--FOPEN takes directory name, filename and access mode as parameters and

--returns a file handle

v_file_handle:=UTL_FILE.FOPEN (‘D:\oracle\admin\oracl\bdump’,'alert_oracl.log', 'A');

 

--PUT_LINE takes file handle and the string to be written into file as parameters

UTL_FILE.PUT_LINE (v_file_handle, 'See I can write into the Alert.log file!!!');

UTL_FILE.FCLOSE (v_file_handle);

 

END;

/

 

**If the file name (“alert_oracl.log” in this case) provided is not present in the mentioned directory, it gets created.

6.    Steps to read the first line from a file:

For reading the file in any directory (“D:\oracle\admin\oracl\bdump” in this case), writes the following line in SQL prompt

 

DECLARE

v_file_handle UTL_FILE.FILE_TYPE;

v_line_File varchar2 (30);

 

BEGIN

v_file_handle :=UTL_FILE.FOPEN (‘D:\oracle\admin\oracl\bdump’, ‘alert_oracl.log’, ‘R’);

 

 

--GET_LINE takes file handle and string to contain the line read from the file

- as parameter

UTL_FILE.GET_LINE (v_file_handle, v_line_File);

DBMS_OUTPUT.PUT_LINE (v_line_File);

UTL_FILE.FCLOSE (v_file_handle);

 

END;

/

 

**If you don’t see the output on SQL prompt, may be you didn’t “set serveroutput on”

 

7.    Granting permissions to other DB users:

To grant file read-write permissions to other schemas on the directory earlier mapped by “utl_dir” type the following on SQL prompt

 

GRANT READ, WRITE ON DIRECTORY utl_dir TO PUBLIC;

 

 

8.    Generalized Steps for file processing:

 

a.    Declaring a file handle:

Using UTL_FILE.FILE_TYPE

b.    Opening the file with file handle:

Using UTL_FILE.FOPEN (Param1, Param2, Param3);

Where,

 

Param1:

Is the “Full path” of the folder (directory) specified in oracle parameter file.

Param2:

Is the name of the file being read or written in the above mentioned folder.

Param3:

Indicates the opening mode of the file.

A’ for append mode.

R’ for read mode.

W’ for write mode.

 

c.    Processing the file:

- To write into the file, using procedure UTL_FILE.PUT_LINE (Param1, Param2);

 

Where,

Param1: Is the “file handle”.

Param2: Is the string that has to be written into the file.

 

OR

 

- To read from the file, using procedure UTL_FILE.GET_LINE (Param1, Param2);

Where,

Param1: Is the “file handle”.

Param2: Is the variable that will act as buffer to contain the line read from the file.

 

When a file is read using GET_LINE procedure, file handle moves to point to the next line in the file.

 

d.    Closing the file handle:

Using UTL_FILE.FCLOSE (Param);

Where,

Param: Is the “file handle”.

 

 

9.    Some useful components of the UTL_FILE package:

 

·         IS_OPEN Function: Determines if a file handle is referring to an already open file

·         FSEEK Procedure: Adjusts the file pointer back or forth within the file by the number of bytes specified

·         FOPEN Function: Opens a file for input or output

·         FOPEN_NCHAR Function: Opens a file in Unicode for input or output

·         FREMOVE Procedure: Deletes a disk file, assuming that you have sufficient privileges

·         FGETPOS Function: Returns the current relative offset position within a file, in bytes

·         FRENAME Procedure: Renames an existing file to a new name

·         FFLUSH Procedure: Writes the pending output to a file

·         NEW_LINE Procedure: Writes line terminator to a file

·         FCLOSE_ALL Procedure: Closes all open file handles

 

 

10. Common exceptions:

The common exception rose during file handling with UTL_FILE are

·                     NO_DATA_FOUND: Trying to read the file beyond the last line.

·                     INVALID_PATH: File location specified in FOPEN function is invalid.

·         INVALID_MODE: The mode of opening as parameter(R, W or A) in FOPEN is invalid.

·                     INVALID_FILEHANDLE: File handle is invalid.

·                     NO_DATA_FOUND: Trying to read beyond the end of the file.

·         INVALID_OPERATION: File could not be opened or operated on as requested.

·                     INVALID_FILENAME: The filename parameter is invalid.

·                     ACCESS_DENIED: Permission to access to the file location is denied.

 

Some Important Notes:

·         Sometimes the system might not allow altering the contents of pfile because of loss of sync between spfile and the pfile. In that case restart the database service and then alter the spfile.

·         Only the directories created in the file system of the oracle server machine can be accessed by UTL_FILE. Though the same files can be accessed from the client side, given that they have been granted the permission to do so. The files in the client machine cannot be accessed with UTL_FILE package

·         The above examples are given considering the windows environment. For UNIX based environment use the UNIX file system notations.

 

For detailed description of contents in UTL_FILE package and their use, please try the links mentioned in the references.

References:

1.    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_file.htm#BABDEJDH

2.      http://www.devshed.com/c/a/Oracle/Reading-Text-Files-using-Oracle-PLSQL-and-UTLFILE/

3.      http://www.adp-gmbh.ch/ora/plsql/utl_file.html