How to use utl_file to read and write to OS from Oracle
How use utl_file to read and write to OS?
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
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.
location =>'DEMOS', -- previously defined driectory name
open_mode =>'w' -- write
UTL_FILE.PUT_LINE(the_file,'An example of using utl_file');
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.