UTL_FILE Exceptions in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

UTL_FILE Exceptions in Oracle with Examples

In this article, I am going to discuss UTL_FILE Exceptions in Oracle with Examples. Please read our previous article where we discussed UTL_FILE Package in Oracle.

UTL_FILE Exceptions in Oracle

Until now we have learnt about the file operations using the UTL_FILE. Now, let’s try to learn about the exceptions in the UTL_FILE. Oracle made some pre-defined exceptions regarding the UTL_FILE package.

Exception Name	Description
INVALID_PATH	File Location Invalid
INVALID_MODE	The open_mode parameter in FOPEN is invalid
INVALID_FILEHANDLE	The file handle is invalid.
READ_ERROR	An operating system error occurred during the read operation.
WRITE_ERROR	An operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
INVALID_OPERATION	The file could not be opened or operated on as requested.

Let’s take an example and learn more about the exceptions.

Example:

First, let’s check the directories in the schema.

select * from all_directories;

From the output, we can see only one directory that we have already created using the MY_DIRECTORY name. We can see the con_id for the directory which mentions the pluggable database.

In the example, we will try to create a procedure with the name read_any_file. So, we are creating a dynamic procedure that reads the file which takes two parameters as directory and file_name. This procedure will read it and output it in the dbms_output. Let’s have a look at the statement.

create or replace procedure read_any_file
(P_dir in varchar2, p_file_name in varchar2)
is
l_file UTL_FILE.file_type;
l_text         VARCHAR2(32767);
begin
  l_file := UTL_FILE.fopen(P_dir, p_file_name, 'r');
  begin
           loop
          UTL_FILE.get_line(l_file, l_text);
          DBMS_OUTPUT.put_line( l_text);
           end loop;
   exception 
          when no_data_found then
          DBMS_OUTPUT.put_line('end file');
          UTL_FILE.fclose(l_file);
   end;

exception 
          when UTL_FILE.invalid_operation then
         DBMS_OUTPUT.put_line('can not open the file,invalid file name');
           when UTL_FILE.read_error then
         DBMS_OUTPUT.put_line('can not be read');
end read_any_file;

So, here we have created a procedure with the name read_any_file which takes two parameters as directory and file name. There are other variables that are declared l_file which is the file name and l_text which is the variable that stores the data.

In this code, we have two begin and end. The first begin and end are considered the main block and the second beginning and end are considered as a small block.

The main block will be used to open and close the file. When the main block begins the file will be open to read_only mode. The block will end by closing the file.

l_file := UTL_FILE.fopen(P_dir, p_file_name, ‘r’);

The file operations will be similar to subprogram fopen with the parameters directory name, file_name, and mode of the file. Now, the directory name and file name will be given dynamically from the procedure.

The small block will contain the main code. This block will contain a loop that will get the line from the file and output the line using put_line. The exception will take place only if no_data_found. If no_data_found then display the end file and then close the file. If the small block couldn’t handle the exception, then the exception will go to the main block.

exception 
          when UTL_FILE.invalid_operation then
         DBMS_OUTPUT.put_line('can not open the file,invalid file name');
           when UTL_FILE.read_error then
         DBMS_OUTPUT.put_line('can not be read');
end read_any_file;

We have used this exception. The UTL_FILE package will have an invalid operation exception and then print “cannot open the file, invalid file”. The other exception is the read_error exception then prints “cannot be read”. Then close the procedure. Now, let’s try to execute the code.

Now, the procedure is compiled successfully. Let’s try to test this procedure by executing the procedure.

execute read_any_file(‘MY_DIRECTORY’,’sample2.txt’);

Here we are calling the procedure read_any_file with parameters directory_name and file name. Let’s try to execute this statement.

We can see the end file is mentioned in the output. So, this execution had taken the first exception. So, this is executed because the directory is present and the file is present inside the directory. Let’s now check the other method.

execute read_any_file(‘MY_DIRECTORY’,’sampdddle.txt’);

We can see the directory is present but the file name is present in the directory. Let’s now try to run this statement and see which exception it chooses.

So, we can see the error showing an invalid filename. So, the file doesn’t exist in the directory.

In the next article, I am going to discuss UTL_MAIL Package in Oracle with Examples. Here, in this article, I try to explain UTL_FILE Exceptions in Oracle with Examples. I hope you enjoy this UTL_FILE exception in the Oracle article.