Writing to OS files

UTL_FILE

If you want to use PL/SQL write directly to a OS file (Note it will use a package that is part of the ORACLE database)

To make sure that the package is already installed do the following at the SQL Prompt

SQL>DESC UTL_FILE

You should get an output, if you do not get an output, then the package is not installed in your Oracle system and you need to install

SIMPLE EXAMPLE

First you must choose or Create an OS directory where you are going to save the XML file

Let me assume we have a directory called C:\REALSOFT (NOTE: if you are using UNIX, stick to UNIX conventions)

1) Connect to SQL*Plus with SYS account (connect as sysdba)

2) Create an DIRECTORY database object that will point to the physical REALSOFT directory

§ CREATE DIRECTORY XML_DIR as ‘c:\REALSOFT’;

§ Check that the directory is created by issuing the following command

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- ------------------------------ ---------------------

SYS XML_DIR C:\REALSOFT

Now remember than whenever we use XML_DIR in PL/SQL it is pointing to C:\REALSOFT

In order to Read and Write to this directory, we need to grant access permission to users who shall be using these directories

§ GRANT READ, WRITE on DIRECTORY XML_DIR to SCOTT;

4) The following procedure shall create a file called Ammar.txt and fill it with names of the employees in the EMP table.

§ MAKE SURE That you now connect to SCOTT

SQL> show USER

USER is "SCOTT"

SQL> Create or Replace procedure write_OS as

2 f_name utl_file.file_type;

3 cursor c1 is select ename from emp;

4 rec c1%rowtype;

5 begin

6 f_name := utl_file.fopen('XML_DIR','ammar.txt','W'); -- open file Ammar.xml for write

7 utl_file.put_line(f_name,'Employee Names on '|| sysdate); -- write a header with current date

8 open c1;

9 loop

10 fetch c1 into rec;

11 exit when c1%notfound;

12 utl_file.put_line (f_name, rec.ename); -- write the employee name

13 end loop;

14 utl_file.put_line(f_name,' End of REALSOFT Employees '); -- end the file with a footer

15 close c1;

16 utl_file.fclose(f_name); -- make sure that you close the file

17* end;

SQL> /

5) Test the procedure by executing it

§ SQL> execute write_OS

PL/SQL procedure successfully completed.

6) Check the output

7) C:\realsoft>dir

Volume in drive C is VAIO

Volume Serial Number is 54A5-8EF0

Directory of C:\realsoft

12/25/2007 11:48 AM <DIR> .

12/25/2007 11:48 AM <DIR> ..

12/25/2007 11:48 AM 158 ammar.txt

1 File(s) 158 bytes

2 Dir(s) 1,240,530,944 bytes free

C:\realsoft>type ammar.txt

Employee Names on 25-DEC-07

SMITH1

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

Bassel

FORD

MILLER

End of REALSOFT Employees

The UTL_FILE package is a nice package a provides more functionality than just writing and reading from OS files. It allows you to furthermore copy files, rename file etc

PROCEDURE FCOPY

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SRC_LOCATION VARCHAR2 IN

SRC_FILENAME VARCHAR2 IN

DEST_LOCATION VARCHAR2 IN

DEST_FILENAME VARCHAR2 IN

START_LINE BINARY_INTEGER IN DEFAULT

END_LINE BINARY_INTEGER IN DEFAULT