Oracle File manipulation in oracle with UTL_FILE package

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.

1)NEW_LINE procedure: The NEW_LINE procedure writes one or more new line terminator to the file. The syntax to use this procedure is,

UTL_FILE.NEW_LINE (
file IN FILE_TYPE,
lines IN NATURAL := 1);

file is the name of the file handle that is open by FOPEN/FOPEN_NCHAR function.
lines is the number of line terminators written to the file.

2)PUT procedure: The PUT procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be opened by FOPEN/FOPEN_NCHAR function for write operation. The syntax is,

UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);

The maximum size of the buffer parameter is 32767 bytes. The default value is 1024 bytes.

Note that the sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

3)PUT_LINE procedure: The PUT_LINE procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. This procedure terminates the line with the line terminator. The syntax to use of this procedure is,

UTL_FILE.PUT_LINE (
file IN FILE_TYPE,
buffer IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);

file and buffer is same as PUT procedure. Autoflash determines whether to flush to disk after write operation.

4)PUT_LINE_NCHAR procedure: The PUT_LINE_NCHAR procedure is used to write in unicode instead of database character set text string into text file. The syntax of this procedure is,

UTL_FILE.PUT_LINE_NCHAR (
file IN FILE_TYPE,
buffer IN NVARCHAR2);

5)PUT_NCHAR procedure: The PUT_NCHAR is used to write in unicode instead of database character set text string into text file. The syntax for using this procedure is,

UTL_FILE.PUT_NCHAR (
file IN FILE_TYPE,
buffer IN NVARCHAR2);

6)PUTF procedure: The PUTF procedure is like PUT procedure but while writing to file you can format string with the PUTF procedure. The syntax is,

UTL_FILE.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]);

The Format parameter can contain text as well as the formatting characters \n and %s.

The \n is the line terminator.
The %s is the substitute with the string value of the next argument in the argument list.

7)PUTF_NCHAR procedure: The PUTF_NCHAR procedure is like PUT_NCHAR procedure but while writing to file you can format string with the PUTF_NCHAR procedure. The syntax is,

UTL_FILE.PUTF_NCHAR (
file IN FILE_TYPE,
format IN NVARCHAR2,
[arg1 IN NVARCHAR2 DEFAULT NULL,
. . .
arg5 IN NVARCHAR2 DEFAULT NULL]);


8)PUT_RAW function: The PUT_RAW function accepts as input a RAW data value and writes the value to the output buffer. The syntax of this function is,

UTL_FILE.PUT_RAW (
fid IN utl_file.file_type,
r IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE);