UTL_FILE:
UTL_FILE is one of the Oracle built-in packages. Using this, data from the DB can be written to a flat file or vice versa. The attributes supported by UTL_FILE package are W (overWrite), A (Append), R (Read).
Before starting to use the UTL_FILE package, the below needs to be performed.
§ A parameter is to be set in the parameter file, which by default is 'init.ora'.
Add a parameter in this file as below
utl_file_dir=D:\oracle\UTLDIR
where 'D:\orcle\UTLDIR', defined by the user, exists in the system that hosts the Oracle Server.
The below example briefs the use of UTL_FILE package, in Oracle 9i. The file_insert procedure, inserts records into a flat file, dept.txt in our example, in the location mentioned in the init.ora. The del_rec procedure, deletes a record from the file based on the key (deptno) given as argument. The file_data procedure, displays the contents of the file.
CREATE OR REPLACE PROCEDURE file_insert (rec IN VARCHAR)
AS
/*** f1 is a file handle and the type of it is UTL_FILE Type ***/
f1 UTL_FILE.FILE_TYPE;
BEGIN
/*** The file, dept.txt is appended with
the record given as the argument ***/
/*** If the file does not exist, the file is created ***/
/*** FOPEN is a function inside the UTL_FILE package,
which opens the file ***/
/*** Here the file is opened in APPEND mode ***/
f1 := UTL_FILE.FOPEN ('D:\oracle\UTLDIR', 'dept.txt', 'A');
/*** PUT_LINE is a procedure inside the UTL_FILE package,
which writes into the file with a return (enter)***/
/*** this procedure takes as arguments,
the file handle and the string to be inserted ***/
UTL_FILE.PUT_LINE (f1, rec);
/*** FCLOSE is a procedure inside the UTL_FILE package,
which closes the file ***/
UTL_FILE.FCLOSE (f1);
END;
CREATE OR REPLACE PROCEDURE del_rec (col1 IN VARCHAR)
AS
/*** File Handle ***/
f1 UTL_FILE.FILE_TYPE;
TYPE file_rec IS TABLE OF VARCHAR(100) INDEX BY BINARY_INTEGER;
rec file_rec;
i INT := 0;
BEGIN
/*** Here the file is opened in READ mode ***/
f1 := UTL_FILE.FOPEN ('D:\oracle\UTLDIR', 'dept.txt', 'R');
/*** The records are read one by one using GET_LINE ***/
BEGIN
i := i + 1;
/*** GET_LINE procedure in the UTL_FILE package
is to read a record from a file ***/
/*** the arguments are the file handle and
a variable to store the string ***/
/*** Here all the records are collected in a PL/SQL table ***/
UTL_FILE.GET_LINE (f1, rec (i));
EXCEPTION
WHEN NO_DATA_FOUND THEN
/*** When a record is not found, the file is closed ***/
UTL_FILE.FCLOSE (f1);
EXIT;
END;
END
/*** The file is again opened in WRITE mode ***/
/*** The file if already exists, is overwritten,
else created and written ***/
f1 := UTL_FILE.FOPEN ('D:\oracle\UTLDIR', 'dept.txt', 'W');
/*** here the file is actually overwritten
without the record which has the col1,
given as an argument to this procedure ***/
FOR r IN rec.FIRST..rec.LAST
IF SUBSTR (rec (r), 1, INSTR (rec (r), ',') - 1) <> col1 THEN
UTL_FILE.PUT_LINE (f1, rec (r));
END IF;
END
UTL_FILE.FCLOSE (f1);
END;
CREATE OR REPLACE PROCEDURE file_data
AS
f1 UTL_FILE.FILE_TYPE;
rec VARCHAR(100);
BEGIN
/*** file is opened in READ mode ***/
f1 := UTL_FILE.FOPEN ('D:\oracle\UTLDIR', 'dept.txt', 'R');
/*** each record is fetched from the file and printed in the console ***/
BEGIN
UTL_FILE.GET_LINE (f1, rec);
DBMS_OUTPUT.PUT_LINE (rec);
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE (f1);
EXIT;
END;
END
END;
Now, when the executing the above three procedures,
EXECUTE file_insert
('10,Accounts,
PL/SQL procedure successfully completed.
EXECUTE file_insert
('20,Admin,
PL/SQL procedure successfully completed.
EXECUTE file_insert
('30,Operations,
PL/SQL procedure successfully completed.
EXECUTE del_rec ('10')
PL/SQL procedure successfully completed.
EXECUTE file_data
20,Admin,
30,Operations,
PL/SQL procedure successfully completed.
Thus is the illustration of UTL_FILE package, in all the three modes, viz., W (overWrite), A (Append), R (Read), using the commonly used procedures and functions in the package, for basic UTL_FILE operations.