How to use utl_file to read and write to OS from Oracle

by neelima
(hyderabad)

How use utl_file to read and write to OS?


UTL_FILE is a package supplied with Oracle PL/SQL so the short, flippant, and not very helpful, answer is to use it in the same way as any other PL/SQL package.

However, to expand on that brief answer, one thing you can't do with UTL_FILE is to read form or write to any location on the server, for security reasons nor can you use it to download files to or upload files from the client. Files have to be transferred to and from the sever using FTP.

Before using UTL_FILE you have to define the locations (directories) that will be used (with the CREATE DIRECTORY command) and grant read and/or write access to those directories to the user that will be running UTL_FILE (with the GRANT <privilege> ON DIRECTORY command).

Assuming that you're using Linux/UNIX here is an example of defining directories and granting access. Note, these commands have to be run as SYSTEM or as a user with the CREATE ANY DIRECTORY system privilege.

SQL> CREATE DIRECTORY demos AS '/u01/docs/demos';
SQL> GRANT READ ON DIRECTORY demos TO hr;
SQL> GRANT WRITE ON DIRECTORY demos TO hr;


In the above example we've defined an Oracle directory (i.e. it's not visible form the o/s) called "demo" and we've granted read and write to the user called "hr". This means that no users, other than sys or system, can read from or write to that directory.

Now that we've defined the directory that we will be using and granted access to it, what can we do with UTL_FILE? Well, the purpose of UTL_FILE is to read and write text files so it provides a number of ways of doing that (GET_LINE, PUT, PUT_LINE etc.) as well as supporting operations such as opening and closing files. The full list of subroutines and their description is available from Oracle at http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm.

Let's look at an example of how to open and write to a file in PL/SQL.


DECLARE
   the_file UTL_FILE.FILE_TYPE;
BEGIN

   the_file :=
     UTL_FILE.FOPEN (
     location =>'DEMOS', -- previously defined driectory name
     filename =>'utl_file_plsql_demo',
     open_mode =>'w' -- write
     );

   UTL_FILE.PUT_LINE(the_file,'An example of using utl_file');

END;


The above example opens a file called "utl_file_plsql_demo" and writes a line of text to it. We don't close the file explicitly but it will be automatically closed by Oracle when the block exits. Another thing to note is that the directory name is stored in uppercase in the database unless you specify the directory name in quotes when defining it.

SQL> CREATE DIRECTORY "demos" AS '/u01/docs/demos';

To see the directories to which you have been granted access you can query ALL_DIRECTORIES. This only shows those directories which you have been granted read or write privileges (unless you query it as sys or system). The sys and system users can also query DBA_DIRECTORIES to see all the directories that have been defined.

For more help with Pl/SQL see our series of PL/SQL tutorials starting here. Follow this link for information about training options available.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.