UTL_FILES

Qns: What is UTL_FILES?
The UTL_FILE is a package that allows users read and write to operating system files on the server serially using PL/SQL. This package first became available with the release of Oracle 7.3 (PL/SQL version 2.3). It can be used from programs on the database or from client-side applications such as those created by Oracle Forms.This package allows you to interact with.ini files, load data from files directly to the database, create flexible interfaces to legacy systems, and generate reports without worrying about the buffer restrictions of dbms_output.

UTL_FILE is a powerful tool as long as we know it’s limitations before using it .

Exception section in the below two examples are left blank as they are discussed in the following questions Let’s now take a look at a very basic example of using UTL_FILE to read from a file:

1 v_FileHandle UTL_FILE.FILE_TYPE;
2 v_Buffer VARCHAR2(100);
3 v_SuppName VARCHAR2(100);
4 Begin
5 v_FileHandle := UTL_FILE.FOPEN(‘C:\Supplier’, ‘Suppname.txt’, ‘r’);
6 UTL_FILE.GET_LINE(v_FileHandle, v_Buffer);
7 v_SuppName := v_Buffer;
8 UTL_FILE.FCLOSE (v_FileHandle);
9 Exception
10 ~
11 End;

Above is a simple example to read from a file using UTL_FILE.
On line 1 we are declaring a FILE_TYPE variable. This works as a handle to files that you open with UTL_FILE. Line 5 shows that FOPEN is a function to open a file for reading. and it’s return value is the handle to the file that is opened. Line 6 we are reading a line of the file into our v_Buffer variable. Then after reading a line we store the contents of the buffer into our v_SuppName variable on line 7.
Finally on line 8 we close the File.

Now let’s look at an example of using UTL_FILE to write to a file:
1 v_FileHandle UTL_FILE.FILE_TYPE;
2 v_TstString VARCHAR2(20) := ‘Introduction to UTL’;
3 Begin
4 v_FileHandle := UTL_FILE.FOPEN(‘C:\test’, ‘Intro.txt’, ‘w’);
5 UTL_FILE.PUT_LINE (v_FileHandle, v_TstString);
6 UTL_FILE.FCLOSE (v_FileHandle);
7 Exception
8 ~
9 End;
We have file handle declared on line 1.
Please Note that the last parameter on line 4 is different from out last example ,the last parameter is a w rather than an r. This means that we are opening the file to write. If the file doesn’t exist it will be created. If it already exists it will be erased. Similarly we could have used an a in the third parameter to write in append mode. On line 5 we write a line to the file, in this case we will write “Introduction to UTL” to the file Intro.txt. The rest of the program is the same as the previous example.

Qns: What are Exceptions in UTL_FILES
The syntax of the util_files exception is as follows
UTL_FILE.
Like
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN


Following are the exceptions:
INVALID_PATH : Raised when Directory / File name is not valid
INVALID_MODE :Raised when File Mode is not valid
INVALID_FILEHANDLE :Raised when file handle is not open
INVALID_OPERATION :Raised when not having proper privileges to open a file.
READ_ERROR :Raised for OS error while trying to read .

WRITE_ERROR Raised for OS error while trying to write .
INTERNAL_ERROR :Unspecified error
NO_DATA_FOUND : Raised when end of the file is reached
INVALID_MAXLINESIZE : Raised when the amount of data read is large for the buffer.

Qns : What do you Mean by File Handler in UTL Files?
This works as a handle to files that you open with UTL_FILE, and will be passed as a parameter to the majority of UTL_FILE functions and procedures. Like we declared in our example above 1 v_FileHandle UTL_FILE.FILE_TYPE;

Qns : What is the syntax of UTL_FILES?
Explained in the example above

Qns: Where you Defined Path of UTL_FILE?
You must explicitly list all directories including subdirectories that you wish UTL_FILE to have access to in the init.ora file. This is done through the UTL_FILE_DIR parameter. You will probably need to add the UTL_FILE_DIR parameter the first time you try to set up UTL_FILE. The UTL_FILE_DIR should be only listed once, and if there are multiple paths you are listing they should be separated by comas.
Paths in your calling program need to match how they are defined in the UTL_FILE_DIR parameter exactly even if your OS is not case sensitive.
example:
UTL_FILE_DIR = /u05/home/output, /output
or
UTL_FILE_DIR = *
You can check the paths assigned to UTL_FILE with the query
SELECT name, value from v$parameter where name like ‘%utl%’;