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.
|File Location Invalid
|The open_mode parameter in FOPEN is invalid
|The file handle is invalid.
|An operating system error occurred during the read operation.
|An operating system error occurred during the write operation.
|Unspecified PL/SQL error
|The file could not be opened or operated on as requested.
Let’s take an example and learn more about the exceptions.
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.
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.
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.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.