File Handling for Beginners

         In this article, we will deal about file handling in PLSQL. This article will give a brief idea about file handling for rookie developers. To read or write any files stored in the operating system, the user need to have read or write permission for the directory. Prior to that, to access a file we need to supply the destination folder of the file as well as the name of the file.

         There are two ways to specify the directory location of the file. First this can be mentioned against UTL_FILE_DIR parameter in the database initialisation file.Secondly one can create a directory object and use this object reference. This approach is introduced in Oracle 9i and is recommended to use.

Creation of Directory: 

       Directory is owned by SYS account. Only a user with Create any directory privilege can create a directory. Syntax to create it is:

 CREATE DIRECTORY <DIRECTORY_NAME> AS <DESTINATION FOLDER>  

where
<DIRECTORY_NAME>  is the name of directory object. EG: UTLTEST
<DESTINATION FOLDER> is the name of the folder in the operating system
eg: /home/oracle/utltest

Let me create a test directory:

 CONN SYS/<password> as SYSDBA  
 CREATE DIRECTORY UTLTEST AS '/home/oracle/utltest';  
 -- NOTE this directory path is applicable to unix environment  

Metadata about this object can be found in ALL_DIRECTORIES data dictionary view.

Once a directory is created, our schema user(HR in below examples) need read write permission granted. SYS DBA need to assign these privilege:

 GRANT READ, WRITE ON DIRECTORY UTLTEST TO HR;  

Opening a file:

       Before we can read or write into a file we must open it. UTL_FILE function allows us to open a file in three mode Read , Write and append mode. This function returns a file handler of  type FILE_TYPE in UTL_FILE package. It takes parameters file location, file name and mode to open the file. The file location parameter should represent a valid directory name. Combination of file location and file name need to give the full path of the file. As far as file mode is concerned, use 'R' for Read mode,  'W' for write mode and 'A' for append mode where we can read and write the file. For Read and Append mode the file must exist when we try to open the file.

Writing to a file:

      To write to a file, we have UTL_FILE programs like PUT, PUT_LINE, NEW_LINE, PUTF and FFLUSH are available. In the example below PUT_LINE procedure is used. 

Reading from a file:

       GET_LINE procedure helps us to read from a file. In the second example, this procedure is used. This procedure takes file handle as in parameter and a VARCHAR2 parameter to return the line read from the file. This out parameter need to be large enough to accomodate the whole line read else VALUE_ERROR exception While reading from a file using GET_LINE inside a loop, if the pointer goes beyond the end of file, NO_DATA_FOUND exception is raised. So care needs to be taken to handle this.

Example 1: Writing into a file:

Below is an example code of writing into a CSV file. Run this by connecting as HR user

 DECLARE  
 FH UTL_FILE.FILE_TYPE;  
 L_STRING VARCHAR2(1000);  
 BEGIN  
 FH := UTL_FILE.FOPEN('UTLTEST','EMPLOYEES' || TO_CHAR(SYSDATE,'DDMMYYHHMISS') || '.CSV','W');  
 FOR EMP_CUR IN (SELECT * FROM EMPLOYEES)  
 LOOP  
 --L_STRING VARCHAR2(1000);  
 L_STRING := EMP_CUR.EMPLOYEE_ID || ','  
          || EMP_CUR.FIRST_NAME || ','  
          || EMP_CUR.LAST_NAME || ','  
          || EMP_CUR.EMAIL || ','  
          || EMP_CUR.PHONE_NUMBER || ','  
          || EMP_CUR.HIRE_DATE || ','  
          || EMP_CUR.JOB_ID || ','  
          || EMP_CUR.SALARY || ','  
          || EMP_CUR.COMMISSION_PCT || ','  
          || EMP_CUR.MANAGER_ID || ','  
          || EMP_CUR.DEPARTMENT_ID;  
 UTL_FILE.PUT_LINE(FH,L_STRING);  
 END LOOP;  
 UTL_FILE.FCLOSE(FH);  
 END;  

Output:

 PL/SQL procedure successfully completed.  

You can now see a file created in /home/oracle/utltest directory.

Example 2: Reading from a file:

Run this by connecting as HR user. In this example we read the previously created file and print it in screen

 DECLARE  
 FH UTL_FILE.FILE_TYPE;  
 L_LINE VARCHAR2(32767);  
 BEGIN  
 FH := UTL_FILE.FOPEN('UTLTEST','EMPLOYEES290312075110.CSV','R');  
 LOOP  
 UTL_FILE.GET_LINE(FH,L_LINE);  
 DBMS_OUTPUT.PUT_LINE(L_LINE);  
 END LOOP;  
 EXCEPTION   
 WHEN NO_DATA_FOUND THEN  
  UTL_FILE.FCLOSE(FH);  
 END;  

Contents of the previously created file are displayed on the screen. Only a part of the output is shown below.

 200,Jennifer,Whalen,JWHALEN,515.123.4444,17-SEP-87,AD_ASST,4400,,101,10  
 201,Michael,Hartstein,MHARTSTE,515.123.5555,17-FEB-96,MK_MAN,13000,,100,20  
 202,Pat,Fay,PFAY,603.123.6666,17-AUG-97,MK_REP,6000,,201,20  
 203,Susan,Mavris,SMAVRIS,515.123.7777,07-JUN-94,HR_REP,6500,,101,40  
 204,Hermann,Baer,HBAER,515.123.8888,07-JUN-94,PR_REP,10000,,101,70  
 205,Shelley,Higgins,SHIGGINS,515.123.8080,07-JUN-94,AC_MGR,12000,,101,110  
 206,William,Gietz,WGIETZ,515.123.8181,07-JUN-94,AC_ACCOUNT,8300,,205,110  

There are various other methods like FCOPY, FREMOVE, FRENAME, FGETATTR which allows us to manipulate files. More information on these can be found here

Reference :

1. Oracle PL/SQL Programming  5th Edition  by Steven Feuerstein, Bill Pribyl

2. Oracle 11G Release documentation on UTL_FILE package

Cleanup scripts:

 CONN SYS/<password> as sysdba  
 DROP DIRECTORY UTLTEST;  

Cheers,

Vijay