File manipulation in oracle with UTL_FILE package -Part 1

UTL_FILE package has various subprograms which helps us file manipulation in oracle.
In this part file opening and closing functions and file managing functions will be discussed. Below is the list of the subprograms of UTL_FILE package along with their works related in this area.

1)FOPEN function: The FOPEN function of UTL_FILE package opens a file. You can open 50 files at a time.
The syntax of FOPEN function is,

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

where, location is the directory name of the file.

file_name is the name of the file without directory path.

open_mode can have value of 'r' or 'w' or 'a' or 'rb' or 'wb'. Where,
r = read text
w = write text
a = append text
rb = read byte mode
wb = write byte mode
ab = append byte mode

If the file does not exists on the file system and yet you try to open the file with "a" or "ab" mode then new file is created and opened in write mode.

The max_linesize parameter specify the maximum number of characters in each line including new line character. The default value of this parameter is 1024 which means if the linesize is greater than 1024 characters then only 1024 characters will be read. The maximum value of this parameter can be specified to 32767.

The returning file handler must be specified of type UTL_FILE.FILE_TYPE.

2)FOPEN_NCHAR function: The FOPEN_NCHAR function is similar to FOPEN function in terms of parameters and return types but it is used to open a file in Unicode for input or output. The syntax is,

UTL_FILE.FOPEN_NCHAR (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

3)FREMOVE procedure: The FREMOVE procedure remove a file from OS file system. The syntax is,
UTL_FILE.FREMOVE (
location IN VARCHAR2,
filename IN VARCHAR2);

4)FRENAME procedure: The FRENAME procedure rename an existing file to a new name. It is just like "mv" command on unix, rename/ren command on windows. The syntax is,

UTL_FILE.FRENAME (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);

The default is no overwrite if one already exist in the destination directory.

5)FCOPY procedure: The FCOPY procedure copy contents from one file to another newly created file. By default whole contents of the file is copied if start_line and end_line parameters are not specified of the procedure. The syntax is,

UTL_FILE.FCOPY (
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line IN BINARY_INTEGER DEFAULT 1,
end_line IN BINARY_INTEGER DEFAULT NULL);


6)FCLOSE procedure: The FCLOSE procedure closes an open file identified by a file handle. The syntax is,

UTL_FILE.FCLOSE (
file IN OUT FILE_TYPE);

Note that, if there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

7)FCLOSE_ALL Procedure: The FCLOSE_ALL procedure closes all open file handles for the session. The syntax is,

UTL_FILE.FCLOSE_ALL;

Example with these procedures/functions:

Create two directory S_DIR and D_DIR which is corresponds C and D drive respectively. Then a PL/SQL which at first create a file under S_DIR named file1.txt using FOPEN and FCLOSE.
Copy file from S_DIR/file1.txt to D_DIR/copy_of_file1.txt.
Move file from S_DIR/file1.txt to to D_DIR/copy2_of_file1.txt.
Finally remove the file from D_DIR/copy_of_file1.txt
At last we will get copy2_of_file1.txt under D: drive.

SQL> create or replace directory S_DIR as 'C:';

Directory created.

SQL> create or replace directory D_DIR as 'D:';

Directory created.

SQL> DECLARE
2    fileHandler1 UTL_FILE.FILE_TYPE;
3  BEGIN
4    fileHandler1 := UTL_FILE.FOPEN('S_DIR', 'file1.txt', 'a');
5    UTL_FILE.FCLOSE(fileHandler1);
6    UTL_FILE.fcopy('S_DIR','file1.txt','D_DIR','copy_of_file1.txt');
7    UTL_FILE.FRENAME('S_DIR','file1.txt','D_DIR','copy2_of_file1.txt',TRUE);
8    UTL_FILE.FREMOVE('D_DIR','copy_of_file1.txt');
9  END;
10  /

PL/SQL procedure successfully completed.

Related Documents

http://arjudba.blogspot.com/2009/02/file-manipulation-in-oracle-with_22.html