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
