Oracle UTL_FILE Package with Example

Oracle 7.3 introduced the UTL_FILE package. The UTL_FILE package in the Oracle database is used to write data from the Oracle database to the OS file and also read from the OS file to the Oracle database.

Mainly three procedures are given in the UTL_File package to read or write the data. These procedures are put_line(), putf(), and get_line(). The put_line() and putf() procedures are used to write data into an OS file. But the get_line() procedure of the UTL_FILE package is used to read data from an OS file to the Oracle database.

Pre-requisite to Use UTL_FILE Package in Oracle

To write UTL_FILE programs, we must create an alias directory related to the physical directory. Syntax to create alias directory related to a physical directory.

Syntax:-
CREATE OR REPLACE DIRECTORY directoryname
AS 'path';

But to create alias directory user must have 'CREATE ANY DIRECTORY' system privileges, otherwise, the Oracle server returns an error. The database administrator gives ‘CREATE ANY DIRECTORY’ system privileges to the user by the following syntax.

Syntax:-
GRANT CREATE ANY DIRECTORY TO username;

Before performing read, write operations at alias directory the user must have read and write object privileges on that alias directory. The Oracle database administrator must give read, write object privileges on alias directory by using the following syntax.

Syntax:-
GRANT READ, WRITE ON DIRECTORY directoryname TO username;

Example:-

Now let us see it through example. Administrator giving permission of 'CREATE ANY DIRECTORY' system privileges to SCOTT user.

SQL> CONN SYS AS SYSDBA;
Enter password: SYS
Connected.

SQL> GRANT CREATE ANY DIRECTORY TO SCOTT;
Grant succeeded.

Now, connect using scott user and create alias directory.

SQL> CONN scott/tiger;
Connected.

SQL> CREATE OR REPLACE DIRECTORY abc as 'D:\Oracle\';
Directory created.

Finally, grant permission to the scott user for reading and writing data on the directory as the system administrator.

SQL> CONN SYS AS SYSDBA;
Enter password: SYS
Connected.

SQL> GRANT READ, WRITE ON DIRECTORY abc TO SCOTT;
Grant succeeded.

Now, connect to scott user and start writing programs.

Sql> CONN scott/tiger;
Connected.

In oracle, if we want to view alias directory and path related to those directories then we are using ‘ALL_DIRECTORIES’ data dictionary.

Example:-

SQL> DESC ALL_DIRECTORIES;

 Name               Null?    Type
 ------------------ -------- --------------
 OWNER              NOT NULL VARCHAR2(30)
 DIRECTORY_NAME     NOT NULL VARCHAR2(30)
 DIRECTORY_PATH              VARCHAR2(4000)

SQL> 
SELECT DIRECTORY_NAME, DIRECTORY_PATH 
FROM ALL_DIRECTORIES;

DIRECTORY_NAME   DIRECTORY_PATH
--------------   --------------
ABC              D:\Oracle\

Writing or Storing Data Into an OS File

We have to follow four steps to write or store data into an OS file. These four steps are the following:-
1) Declare file pointer variable
2) Open the file by using FOPEN() function
3) Write data into the file using PUTF() or PUT_LINE() procedure
4) Close the file using FCLOSE() function

Step1:- Before we are opening a file, we must declare the file pointer variable in the declare section of the PL SQL block by using FILE_TYPE from the UTL_FILE package.

Syntax:-
Filepointervarname UTL_FILE.FILE_TYPE;

Step2:- Before we are writing data into an OS file, then we must open the file by using FOPEN() function from the utl_file package. This function is used in the executable section of the PL SQL block. The FOPEN() function accepts three parameters and returns the file_type data type.

Syntax:-
filePointerVarname := UTL_FILE.FOPEN('aliasdirectoryname', 'filename', 'mode');

Here aliasDirectoryName must be in capital letter.

Different modes used in fopen() are,
• w:- write mode
• r:- read mode
• a:- append mode

Step3:- To write data into the OS file we can use put_line() or putf() procedure from UTL_FILE package.

Syntax for putf():-
UTL_FILE.PUTF(filepointervarname, 'content');

Syntax for put_line():-
UTL_FILE.PUT_LINE(filepointervarname, format);

Step4:- At last, we must close the file by using fclose() procedure from utl_file package.

Syntax:-
UTL_FILE.FCLOSE(filepointervarname);

Example to Store Data Using Oracle UTL_FILE

Example1:- Write a PL/SQL program that is used to write any content like ‘knowprogram.com’ into an OS file by using the Oracle UTL_FILE package.

Since we have to write the data into a file, so we must open the file in write mode, and if the file is not already available then the Oracle database will itself created the file.

DECLARE
    -- declare file pointer variable
    fp UTL_FILE.FILE_TYPE;
BEGIN
    -- open file
    fp := UTL_FILE.FOPEN('ABC', 'file1.txt', 'w');
    -- write data into file
    UTL_FILE.PUTF(fp, 'knowprogram.com');
    -- close file
    UTL_FILE.FCLOSE(fp);
END;

Output:-

SQL> /
PL/SQL procedure successfully completed.

Here fp is a filePointerVariableName. After executing this PL/SQL program “file1.txt” is created in the ‘D:\Oracle\’ directory. The file1.txt contains the text “knowprogram.com”.

Example2:- Write a PL/SQL program that is used to store all employee names from the emp table into OS file using the Oracle UTL_FILE package.

DECLARE
    -- declare file pointer variable
    fp UTL_FILE.FILE_TYPE;
    -- declare a cursor to fetch data from table
    CURSOR c1 IS SELECT ename FROM emp;
BEGIN
    -- open file
    fp := UTL_FILE.FOPEN('ABC', 'file2.txt', 'w');
    -- loop to fetch data
    FOR i IN c1
    LOOP
        -- write data into file
        UTL_FILE.PUTF(fp, i.ename);
    END LOOP;
    -- close file
    UTL_FILE.FCLOSE(fp);
END;

Output in file2.txt:-

SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER

While retrieving column data from a table if we are using PUTF() procedure then Oracle server store that column data in a horizontal manner within the OS file. To overcome this problem, To store column data in a vertical manner we must use ‘\n’ newline character and also %s access specifier within the second parameter of the PUTF() procedure. This second parameter must be specified within a single quote.

Syntax:-
utl_file.putf(filepointervarname, '%s\n', variablename);

In the previous program replace with below line:-
UTL_FILE.PUTF(fp, '%s\n', i.ename);

Now, execute the PL/SQL program and open the “file2.txt” file. All the data is stored in a vertical manner.

Example3:- In PUTF() procedure we can add our own text.

DECLARE
    -- declare file pointer variable
    fp UTL_FILE.FILE_TYPE;
    -- declare a cursor to fetch data from table
    CURSOR c1 IS SELECT ename FROM emp;
BEGIN
    -- open file
    fp := UTL_FILE.FOPEN('ABC', 'file3.txt', 'w');
    -- loop to fetch data
    FOR i IN c1
    LOOP
        -- write data into file
        UTL_FILE.PUTF(fp, 'Employee Name: %s\n', i.ename);
    END LOOP;
    -- close file
    UTL_FILE.FCLOSE(fp);
END;
/

After execution, the data in file3.txt:-

Employee Name: SMITH
Employee Name: ALLEN
Employee Name: WARD
…………
…………

Generally, we are not using putf() procedure. We can do the same task using the put_line() procedure. The put_line() is a regularly used procedure in place of putf() procedure.

PUT_LINE()

In Oracle, by using the put_line() procedure also we are allowed to write data into an OS file. In this case, we must pass the file pointer variable as the first parameter within the put_line() procedure.

Syntax:-
UTL_FILE.PUT_LINE(filepointervarname, format);

Example using PUT_LINE()

Example4:- Write a PL/SQL program which is used to store all employee name, salary, and hiredate from emp table into OS file using Oracle UTL_FILE package. Use put_line() procedure.

DECLARE
    -- declare file pointer variable
    fp UTL_FILE.FILE_TYPE;
    -- declare a cursor to fetch data from table
    CURSOR c1 IS SELECT * FROM emp;
BEGIN
    -- open file
    fp := UTL_FILE.FOPEN('ABC', 'file4.txt', 'w');
    -- loop to fetch data
    FOR i IN c1
    LOOP
        -- write data into file
        UTL_FILE.PUT_LINE(fp, i.ename||' '||i.sal||' '||i.hiredate);
    END LOOP;
    -- close file
    UTL_FILE.FCLOSE(fp);
END;
/

Output in file4.txt:-

SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 22-FEB-81
………..
………..

In put_line() we don’t explicitly need to tell write data into the new line, it will do itself. In the real-time project, we are always using the put_line() procedure. The putf() procedure is only given for knowledge and interview purposes.

Reading Data From an OS File Using Oracle UTL_FILE

If we want to read data from an OS file and display it into the console, then we are using GET_LINE() procedure from the UTL_FILE package. Before using this procedure we must use read mode (‘r’) in fopen() function.

Before opening the file in reading mode, we should declare a buffer variable in the declare section in PL/SQL block. Normally, the varchar2 data type is taken for buffer.

Syntax:-
utl_file.get_line(filepointervarname, bufferVariableName);

Example1:- Write a PL/SQL program by using the utl_file package which is used to read data from file1.txt, and also display that content in the SQL*Plus tool. The data in file1.txt is,

knowprogram.com

Solution:-

SQL>
DECLARE
    fp UTL_FILE.FILE_TYPE;
    -- declare a buffer variable
    z VARCHAR2(200);
BEGIN
    -- open file in read mode
    fp := UTL_FILE.FOPEN('ABC', 'file1.txt', 'r');
    -- fetch data from file
    UTL_FILE.GET_LINE(fp, z);
    -- display output
    DBMS_OUTPUT.PUT_LINE(z);
    -- close the file
    UTL_FILE.FCLOSE(fp);
END;
/

SQL> /
knowprogram.com
PL/SQL procedure successfully completed.

Example2:- Write a PL/SQL program by utl_file package and read multiple data items from file4.txt and display its content from the file. The data of file4.txt is,

SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 22-FEB-81

Solution:-

SQL>
DECLARE
    fp UTL_FILE.FILE_TYPE;
    -- declare a buffer variable
    z VARCHAR2(200);
BEGIN
    -- open file in read mode
    fp := UTL_FILE.FOPEN('ABC', 'file4.txt', 'r');
    -- fetch data from file
    LOOP
        UTL_FILE.GET_LINE(fp, z);
        -- display each line
        DBMS_OUTPUT.PUT_LINE(z);
    END LOOP;
    -- close the file
    UTL_FILE.FCLOSE(fp);
END;
/

SQL> /
SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 22-FEB-81
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at “SYS.UTL_FILE”, line 106
ORA-06512: at “SYS.UTL_FILE”, line 746
ORA-06512: at line 10

Whenever we are reading multiple data items from an OS file by using the UTL_FILE package then the Oracle server returns an error Ora-1403: no data found when control reaches the end-of-file. To overcome this problem we must handle this exception by using the no_data_found exception name in the exception section of the PL/SQL block. We have to close the file when the control reaches the end of the file.

SQL>
DECLARE
    fp UTL_FILE.FILE_TYPE;
    -- declare a buffer variable
    z VARCHAR2(200);
BEGIN
    -- open file in read mode
    fp := UTL_FILE.FOPEN('ABC', 'file4.txt', 'r');
    -- fetch data from file
    LOOP
        UTL_FILE.GET_LINE(fp, z);
        -- display each line
        DBMS_OUTPUT.PUT_LINE(z);
    END LOOP;
    -- exception section
EXCEPTION
    WHEN no_data_found THEN
    -- close the file
    UTL_FILE.FCLOSE(fp);
END;
/

SQL> /
SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 22-FEB-81
PL/SQL procedure successfully completed.

To load data from an OS file to a database, UTL_FILE gives less performance. Instead of UTL_FILE, we are using the SQL loader tool for this purpose. There we are using a Control file, flat-file to load data from an OS file to text file and it gives better performance.

---

Exercise

The output of the below PL/SQL program is stored horizontally. Can you display each record in a new line without using ‘\n’?

DECLARE
    fp UTL_FILE.FILE_TYPE;
BEGIN
    fp := UTL_FILE.FOPEN('ABC', 'file.txt', 'w');
    UTL_FILE.PUTF(fp, 'Hello');
    UTL_FILE.PUTF(fp, 'World');
    UTL_FILE.FCLOSE(fp);
END;
/

Output data in file.txt:-

HelloWorld

Expected Data in file.txt:-

Hello
World

▼ Hint

ASCII value of 10 is a new-line character.

▼ Answer

The chr(10) also can be used for the new line. The ASCII value of 10 is a new line and chr() function converts the passed value as ASCII value.

UTL_FILE.PUTF(fp, 'Hello' || chr(10) );
UTL_FILE.PUTF(fp, 'World');

To learn Microsoft Azure you can take help of Examsnap Microsoft AZ-104, which provides a user-friendly interface and accurate, reliable content that simulates real exam scenarios by offering a wide range of practice materials created by experts in the field.

Also, Learn,

• How to unlock Scott user in SQL Plus
• Error ORA 01031 insufficient privileges

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search