Back to: Oracle Tutorials for Beginners and Professionals
UTL_FILE Package in Oracle with Examples
In this article, I am going to discuss UTL_FILE Package in Oracle with Examples. Please read our previous article where we discussed DBMS_OUTPUT Package in Oracle. This package will operate with the files.
The UTL_FILE package extends PL/SQL programs to read and write operating system text files. This provides a restricted version of the operating system stream file I/O for the text files. This can access files in operating system directories defined by a CREATE DIRECTORY statement.
Flow Chart of UTL_FILE Package in Oracle:
So, the first step to using the UTL_FILE is to create the directory in the database.
Process of UTL_FILE:
Reading a file:
The first step is to open the file for reading then we will get lines from the text file. If the reading is completed then we will close the file or continue reading the file until the EOF.
Writing or appending to a file:
The first step is to open the file and then we have to use PUT_LINE to insert new lines and then save and close the file.
Prerequisites: To read/write a file we need to create a directory. The directory can be created by only the sys of the system user.
Step 1: Connect to the database as “sqlplus / as sysdba”.
Open the command prompt or login to the database server virtual machine. In our case, we have logged in to the server and then connected as sysdba.
Step 2: Connect to the pluggable database.
Whenever we connect to the database as sysdba we connect to the container database. We have to move to the pluggable database as we have done all the operations in the pluggable database. We can connect to the pluggable database using the below statement.
alter session set container=jayapdb;
We can check the container database name that we are connected to by using “show con_name”.
Step 3: Create a Directory.
We must create a directory in order to perform any file operations. Actually, creating a directory is the task of the DBA.
create directory my_directory as ‘/archives/test/’;
So, the directory is created. As the directory is created every user in the database needs to have privileges on the directory. So, we will grant read and write privileges to all the users in the database.
grant read, write on directory my_directory to public;
Step 4: Create a directory locally.
As we have created a directory test in the archives folder. Let’s now go ahead and create these folders physically on the server.
There are already archive folders present on the server. So, we have created a test folder inside the archives folder.
Step 5: Create a sample file.
Let’s create a sample file named sample.txt and enter some data into it. Let’s enter valid data into the Txt file. We are going to run the select query on the employees table.
select * from employees;
Copy all the output and then paste it into the sample.txt file inside the directory that we have created. In our case, we have created a directory as /archives/test. We are pasting the output in sample.txt present inside the folder.
We have mentioned the first line twice. We will try to use is an advantage and see if the compiler can print the same records. Until now we have created a directory named my_directory and granted permissions for the user and then created the sample text file and then saved the output from the employees table. As the directory and everything are set, let’s now try to learn about the file operations.
Now, let’s try to learn about UTL_FILE and learn about the procedure inside the package UTL_FILE.
GET_LINE in Oracle:
We will be using the GET_LINE procedure inside the package UTL_FILE.
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DIRECTORY'; --CAPITAL
l_filename VARCHAR2(100) := 'sample.txt';
l_text VARCHAR2(32767);
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r');
--Read and output first line.
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line('First Line:' || l_text);
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line('second Line:' || l_text );
--close the file
UTL_FILE.fclose(l_file);
end;
In the above code, we have declared four variables l_file, l_location, l_filename, and l_text. So, in order to read the file, we have to define the variables.
We have declared l_file which the type is UTL_FILE. This file type is the default file type. The variable l_location is the “MY_DIRECTORY” that we have created. The variable l_filename is the file that we need to use to read and modify changes to the file. The variable l_text is varchar2. The first step is to open the file. Below line indicates to open the file.
l_file := UTL_FILE.fopen(l_location, l_filename, ‘r’);
This works with the sub-program fopen. This sub-program takes three parameters as the input – location, file name, and type operation ‘r’ which means read. This means we will open the file for reading. Once the file is opened the cursor will be on the first line. The second step is to read the file.
UTL_FILE.get_line(l_file, l_text);
Dbms_output.put_line(‘First Line:’ || l_text);
So, the get_line will take the first line from the text file and then save it to the variable l_text. Then the cursor will be moved to the second line. The PUT_LINE will print the first line into the buffer and the output will be displayed in dbms_output.
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line(‘second Line:’ || l_text );
Now, the code will read the second line and then print the second line and the cursor will move to the third line. Finally, we use the subprogram fclose to close the file. Let’s try to execute the code and see the output. We should get the same line and line 2 because our sample.txt has the same first line and second line.
We can see the output is the same. We have opened the file and we have read the first lines and then printed the two lines. Now, let’s try to read all the data in the sample.txt file. Let’s see the code for reading all the data in sample.txt.
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DIRECTORY'; --CAPITAL
l_filename VARCHAR2(100) := 'sample.txt';
l_text VARCHAR2(32767);
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, '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');
end;
--close the file
UTL_FILE.fclose(l_file);
end;
So, here we have declared the four variables exactly as same as in the previous example. We have opened the file using the subprogram fopen. The subprogram takes three parameters l_location, l_filename, and the file type is read-only.
Here we are using begin and end because we must write exceptions as well. In the beginning, we inserted a loop that will read the file sample.txt line by line. So, the get_line will read the first line and then print the first and the cursor will go to the second line. This loop will read until the last line of the file sample.txt. So, we need to inform the loop to stop the loop after the last line by creating the exception.
Now, the exception contains if there is no_data_found then print “end file”. Finally, we will close the file using the subprogram using fclose. Let’s execute this code.
We can see that all the employee details present in the sample.txt file are displayed in dbms_output.
PUT_LINE in Oracle:
Until now there is already a text file. We have read that file and displayed the information. Now, let’s try to do more advanced by creating a new text file.
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DIRECTORY'; --CAPITAL
l_filename VARCHAR2(100) := 'sample2.txt';
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'w');
for i in (select * from DEPARTMENTS)
loop
UTL_FILE.put_line(l_file, i.DEPARTMENT_name);
end loop;
UTL_FILE.fclose(l_file);
From the above text file, we can see declarations. We have declared three variables l_file, l_location, and l_filename. The variable l_file is the default file type. The variable l_location is the location of the directory that we are using. The variable l_filename is the variable that is used for the new text file. Now, coming to the code we have to begin and end.
l_file := UTL_FILE.fopen(l_location, l_filename, ‘w’);
The first operation is creating a file with l_file using the subprogram fopen with three parameters l_location, l_filename, and ‘w’ which is writable. We will open the file for writing.
for i in (select * from DEPARTMENTS) loop UTL_FILE.put_line(l_file, i.DEPARTMENT_name); end loop;
We are using the for loop which will open the cursor and read all the information from the departments table. The cursor will write all the information to the l_file using the procedure put_line. We are just pasting the department_name into the l_file using the put_line procedure. After put_line we will close the loop.
We will be closing the file using the subprogram fclose. Now, let’s try to execute the code and see the output. Before running we can check the directory that there is no sample2.txt file present.
The directory doesn’t contain the sample2.txt. Let’s now try to execute the code.
We can see the code is executed successfully. Let’s go ahead and check the file.
We can see the file is created. Let’s open the file and see the details. This file should contain all the department_name details.
So, here we can see all the department name in the file sample2.txt.
Note: we have mentioned “w” in file_type. So, if we re-execute the code again. The entire data will be deleted and the file will be overwritten with the same data. If we need to add a few more lines at the end of the file we need to “A” file_type instead of “w”. Let’s look at the below statement.
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DIRECTORY'; --CAPITAL
l_filename VARCHAR2(100) := 'sample2.txt';
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'w');
for i in (select * from DEPARTMENTS)
loop
UTL_FILE.put_line(l_file, i.DEPARTMENT_name);
end loop;
--close the file
UTL_FILE.fclose(l_file);
l_file := UTL_FILE.fopen(l_location, l_filename, 'A');
UTL_FILE.put_line(l_file, 'ADDITIONAL LINES');
UTL_FILE.fclose(l_file);
end;
We will open the file again and use the append file type. We have mentioned the file_type as “A” and then in the last line we have mentioned ADDITIONAL LINES. Finally, we will close the file after adding the new line. Let’s try to run this code.
So, we can see the code is now executed successfully. Let’s now have a look at the file sample2.txt and see if the last line is added with ADDITIONAL LINES or not.
So, here we can see the last line with ADDITIONAL LINES. So, these are a few examples of the UTL_FILE package and the procedures including procedures.
In the next article, I am going to discuss UTL_FILE Exceptions in Oracle with Examples. Here, in this article, I try to explain UTL_FILE Package in Oracle with Examples. I hope you enjoy this UTL_FILE Package in Oracle article.
