Using the UTL_FILE Package to Perform File I/O (UNIX)
Perform File I/O (UNIX)
Introduction
------------
Starting with PL/SQL version 2.3, the UTL_FILE package is available to read and write to operating system flat files. The UTL_FILE package is available on all platforms, but this article focuses on the UNIX platform only.
Several procedures allow you to open, put text into, get text from, and
close the file. The UTL_FILE runs on the server side, not from the client (TEXT_IO is the package which allows you to read and write from a flat file on the client side).
Permissions
-----------
To maintain file security, all valid directories must be explicitly mentioned in the init
chmod 777
If Oracle creates the file, the permissions on the file are rw-r--r--.
Procedures and Functions
------------------------
Each procedure has exceptions that may be raised during execution. The
package includes the following commonly used procedures and functions:
Package Specification
~~~~~~~~~~~~~~~~~~~~~
The package functions are described in detail in entitled
"PACKAGE UTL_FILE Specification" and are not duplicated here.
Subject: PACKAGE UTL_FILE Specification
------------
INTRODUCTION
------------
The following article introduces the new utl_file package, available with Oracle 7.3, which allows you to perform file I/O from PL/SQL.
-----------
DESCRIPTION
-----------
Prior to Oracle 7.3 there was no easy way of reading and writing to operating system files from a PL/SQL block. This can now be achieved using the new utl_file package and its provided procedures and functions.
This package only supports serial file access at the current time.
-----------------
SECURITY WARNING
-----------------
When using the UTL_FILE package you need to be aware of potential security problems because any I/O performed by this package is effectively performed by the oracle operating system user.
Beacause of this Oracle restricts the directories you are allowed to write too and read from, using the UTL_FILE_DIR init.ora parameter.
The UTL_FILE_DIR init.ora parameter lists directories the utl_file()
package can perform I/O on. This parameter can be set to '*', meaning
I/O can be performed to ANY directory using this package but this would
NOT be recommended.
Unix systems -
You need to make sure that only the "oracle" unix user has write
permissions to the directories listed in
otherwise non-privileged users may be able to bypass security using
symbolic links.
-------------------
SETTING UP UTL_FILE
-------------------
Firstly you need to create the utl_file package as this is NOT done from
any of the commonly run scripts (catalog,catproc etc.).To do this
run utlfile.sql and prvtfile.plb both located in
$ORACLE_HOME/rdbms/admin
from withing Server Manager while connected as sys or internal.
Once the utl_file package has been succesfully created you will need to
manually grant execute to it to any users you wish to use it.
A public synonym is not created for the package so this will need to
be done manually on a user or public level.
------------
RESTRICTIONS
------------
a) Reading and writing to the same file :
Because you can only fopen() a file in ONE of 3 modes (read,write or append),if you want to write and read to a file you would need to do the
following :
1) fopen() the file with 'w' or 'a' mode and write to it.
2) fclose() the file.
3) fopen() the file again in 'r' mode.
b) Note that in Oracle 7.3 - 8.0.5 you are limited to a maximum of
10 open files at any one time. In 8.0.6 this limit is increased to
50 open files.
---------
TYPES
---------
FILE_TYPE - File handle which is used with most calls.
TYPE file_type IS RECORD (id BINARY_INTEGER);
----------
EXCEPTIONS
----------
INVALID_PATH
INVALID_MODE
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
WRITE_ERROR
INTERNAL_ERROR
INVALID_MAXLINESIZE
------------------------
PROCEDURES AND FUNCTIONS
------------------------
Example Usage
~~~~~~~~~~~~~~
The following is a simple example demonstrating how to set up your environment,and includes a small PL/SQL procedure that writes out to an operating system flat file.
1. Edit the initSID.ora file.
The UTL_FILE_DIR parameter must be added to the init
This parameter should only be listed once. If there are numerous
directory paths, then all paths should be listed with commas or spaces
separating them.
Note that the wildcard (*) can be used. However, setting
'UTL_FILE_DIR = *' makes any directory accessible to the UTL_FILE
functions and should only be used with great caution. Oracle does
not recommend using the '*' on a production system.
Examples:
UTL_FILE_DIR = /u05/home/output/mydir, /output
or
UTL_FILE_DIR = *
** VERY IMPORTANT ** the Oracle instance must be brought down and back
up for the changes in the init.ora file to be effective.
2. Write a PL/SQL procedure.
The following is a test table named testtab:
Name Null? Type
------------------------------- -------- ----
C1 NUMBER
C2 NUMBER
The following is the data in the testtab table:
C1 C2
---------- ----------
10 25
20 50
Example:
CREATE OR REPLACE PROCEDURE test1 IS
-- file handle of OS flat file
file_handle UTL_FILE.FILE_TYPE;
-- C1 retrieved from testtab
col1 NUMBER;
-- Line retrieved from flat file
--table retrieved_buffer VARCHAR2(100);
abc VARCHAR2(1000);
BEGIN
-- Open file to write into and obtain its file_handle.
file_handle :=
UTL_FILE.FOPEN('/d2/oracle/viscomn/temp','myfile.txt','W');
-- Write a line of text out to the file.
UTL_FILE.PUT_LINE(file_handle, 'this is line 1 as a test');
-- Select the c1 from the testtab table where empno = 7900.
SELECT c1 INTO col1 FROM testtab
WHERE c2 = 25;
-- Using PUTF write text with the col1 argument out to the file.
UTL_FILE.PUTF (file_handle,
'This is the c1 %s when the c2 is %s.\n',
col1,'25');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other stuff');
UTL_FILE.FCLOSE(file_handle);
END;
/
exec test1;
================Inserting data into the table==========================
CREATE OR REPLACE PROCEDURE test2 IS
file_handle UTL_FILE.FILE_TYPE;
myline VARCHAR2(1000);
a testtab.c1%type;
b testtab.c2%type;
begin
-- Open file to write into and obtain its file_handle.
file_handle :=
UTL_FILE.FOPEN('/d2/oracle/viscomn/temp','myfile.txt','r');
loop
begin
UTL_FILE.GET_LINE (file_handle, myline);
exception
when no_data_found then
exit;
end;
a:=substr(myline,1,3);
b:=substr(myline,5,3);
insert into testtab(c1,c2) values(a,b);
end loop;
UTL_FILE.FCLOSE(file_handle);
exception
when no_data_found then
null;
end;
/
exec test2;
3. Compile your PL/SQL procedure and run it.
4. The following is a listing of the /u05/home/output/mydir directory
before running the PL/SQL program:
total 6
-rw-r--r-- 1 usupport dba 2279 Dec 27 16:52 test.sql
The following is a listing of the /u05/home/output/mydir directory
after running the PL/SQL program:
total 8
-rw-r--r-- 1 osupport dba 62 Dec 27 16:53 myfile.txt
-rw-r--r-- 1 usupport dba 2279 Dec 27 16:53 test.sql
The following are the contents of the myfile.txt file:
this is line 1 as a test
This is the c1 10 when the c2 is 25.
The following is output to the SQL*Plus screen:
SQL> execute test1
this is line 1 as a test
PL/SQL procedure successfully completed.
Subject: FUNCTION UTL_FILE.FOPEN Specification
~~~~~~~~~~~~~~~~~~~~~~~
Specification:
FUNCTION fopen(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT 1023 ) RETURN file_type;
Note: MAX_LINESIZE can only be specified from 8.0.5 onwards
Description
FOPEN - opens a file in ONE of the modes specified below.
The file must be in one of the locations made accessible by the
init.ora
Mode Description Valid functions in this mode.
------------------------------------------------------------------------
Write ('w') Opens file for writing. put_line,put,new_line,putf,
Will create the file if
it fflush does not already exist.
Append ('a') Opens file for append. put_line,put,new_line,putf,
The file MUST already it fflush
Read only ('r') Open an existing file get_line for reading.
------------------------------------------------------------------------
If a file is created it will be owned by the owner of the Oracle shadow
process running the instance.
FOPEN returns a file handle of type UTL_FILE.FILE_TYPE. This file handle is to be used as a parameter to other procedures in this package.
Prior to 8.0.5 FOPEN does not take a parameter for the maximum line
size. Thus, the default (which is 1023 on most systems) will be used.
From 8.0.5 onwards a maximum line size can be specified.
NOTE:
FOPEN can be used to open Unix named pipes and similar types of file
if the permissions are set correctly. &p_database_id=NOT>
PARAMETERS:
location - directory location of file
filename - file name (including extention)
open_mode - open mode ('r', 'w', 'a') as described above
max_linesize - maximum number of characters per line, including the
newline character, for this file.
(minimum value 1, maximum value 32767)
RETURN
UTL_FILE.FILE_TYPE handle to open file
EXCEPTIONS
invalid_path - file location or name was invalid
1) 'location' is NOT listed in the init.ora
2) 'location' does NOT exist.
invalid_mode - the open_mode string was invalid
1) 'mode' is not 'a','r' or 'w'.
invalid_operation - file could not be opened as requested
1) Trying to fopen a non-existent file in 'r' or 'a' mode.
2) Trying to open/create a file without OS
permission
invalid_maxlinesize - specified max_linesize is too large or too small
.
Subject: FUNCTION UTL_FILE.IS_OPEN Specification
~~~~~~~~~~~~~~~~~~~~~~~~~
FUNCTION is_open(file IN file_type) RETURN BOOLEAN;
Description:
IS_OPEN - Test if file handle is open
PARAMETERS
file - File handle returned from
RETURN
BOOLEAN - Is file handle open/valid?
Subject: PROCEDURE UTL_FILE.FCLOSE Specification
~~~~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE fclose(file IN OUT file_type);
Description:
FCLOSE - close an open file handle.
PARAMETERS:
file - File handle (Note: File handle must be valid/open)
EXCEPTIONS
invalid_filehandle - not a valid file handle
write_error - OS error occured during write operation
.
PROCEDURE UTL_FILE.FCLOSE_ALL Specification
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE fclose_all;
Description:
FCLOSE_ALL - close all open files for this session
For Emergency/Cleanup use only. FILE_TYPE handles will not be
cleared (IS_OPEN will still indicate they are valid)
PARAMETERS:
file - File handle (open)
EXCEPTIONS
write_error - OS error occured during write operation
Subject: PROCEDURE UTL_FILE.FFLUSH Specification
~~~~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE fflush(file IN file_type);
Description:
FFLUSH - Force physical write of buffered output
IN
file - File handle (open in write/append mode)
EXCEPTIONS
invalid_filehandle - not a valid file handle
invalid_operation - file is not open for writing/appending
write_error - OS error occured during write operation
Subject: PROCEDURE UTL_FILE.GET_LINE Specification
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE get_line(file IN file_type,
buffer OUT VARCHAR2);
Description:
GET_LINE - Get (read) a line of text from the file into 'buffer'.
IN
file - File handle (open in read mode)
OUT
buffer - next line of text in file
EXCEPTIONS
no_data_found - reached the end of file
value_error - line to long to store in buffer
invalid_filehandle - not a valid file handle
invalid_operation - file is not open for reading.
Ie: Was not opened in 'r' mode.
read_error - OS error occurred during read
.
Subject: PROCEDURE UTL_FILE.NEW_LINE Specification
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE new_line(file IN file_type,
lines IN NATURAL := 1);
Description:
NEW_LINE - Write line terminators to file
IN
file - File handle (open in write/append mode)
lines - Number of newlines to write (default 1)
EXCEPTIONS
invalid_filehandle - not a valid file handle
invalid_operation - file is not open for writing/appending
write_error - OS error occured during write operation
Subject: PROCEDURE UTL_FILE.PUT Specification
~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE put(file IN file_type,
buffer IN VARCHAR2);
Description:
PUT - Put (write) text to file
IN
file - File handle (open in write/append mode)
buffer - Text to write
EXCEPTIONS
invalid_filehandle - not a valid file handle
invalid_operation - file is not open for writing/appending
write_error - OS error occured during write operation
Subject: PROCEDURE UTL_FILE.PUTF Specification
~~~~~~~~~~~~~~~~~~~~~~~
Specification:
procedure putf(file IN file_type,
format IN VARCHAR2,
arg1 IN VARCHAR2 DEFAULT NULL,
arg2 IN VARCHAR2 DEFAULT NULL,
arg3 IN VARCHAR2 DEFAULT NULL,
arg4 IN VARCHAR2 DEFAULT NULL,
arg5 IN VARCHAR2 DEFAULT NULL);
Description:
PUTF - Put (write) formatted text to file
Format string can contain special characters thus
'%s' - substitute with next argument.
An empty string will be substituted for a
%s that does not have a corresponding argument.
'\n' - newline (line terminator)
The actual character written in platform specific.
IN
file - File handle (open in write/append mode)
format - Formatting string
arg1 - Substitution argument #1
...
EXCEPTIONS
invalid_filehandle - not a valid file handle
invalid_operation - file is not open for writing/appending
write_error - OS error occured during write operation
Subject: PROCEDURE UTL_FILE.PUT_LINE Specification
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specification:
PROCEDURE put_line(file IN file_type,
buffer IN VARCHAR2);
Description:
PUT_LINE - Put (write) line to file. This writes the supplied text and
then adds a newline character to the output.
IN
file - File handle (open in write/append mode)
buffer - Text to write
EXCEPTIONS
invalid_filehandle - not a valid file handle
invalid_operation - file is not open for writing/appending
write_error - OS error occured during write operation
16 July 2007
How to use UTL_FILE Package
Labels: PL/SQL
Subscribe to:
Post a Comment