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.
<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:
Once a directory is created, our schema user(HR in below examples) need read write permission granted. SYS DBA need to assign these privilege:
Output:
Contents of the previously created file are displayed on the screen. Only a part of the output is shown below.
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>
<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
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.
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 :
Cleanup scripts:
CONN SYS/<password> as sysdba
DROP DIRECTORY UTLTEST;
Cheers,
Vijay
