UTL_FILE
package can be used to read and write files that are located on the server.
PL/SQL programs can be used to read and write operating system text files with
the UTL_FILE package.
UTL_FILE package
provides a restricted version of operating system stream file I/O.I/O
capabilities provided by UTL_FILE package are similar to standard operating
system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but it has certain
limitations.
The
directories from which the files are accessed for reading and writing should be
owned by Oracle with permissions set for read, write and execute for everyone.
Following are some sample scripts of UTL_FIILE package:
Example 1:
Step1:
create a directory create or replace directory ExampleDir as 'F:\Example'
Directory created.
Step
2: Program to open an existing
file in write mode and put line
DECLARE
exfile UTL_FILE.file_type;
BEGIN
exfile :=
UTL_FILE.fopen ('ExampleDir',
'file1.txt',
'w',
32767);
UTL_FILE.put_line (exfile, 'The new line is inserted in the file');
UTL_FILE.fclose (exfile);
END;
/
File
file1.txt will be created in F:\Example directory.
Example
2:
Step1:
create a directory create or replace directory ExampleDir1 as 'F:\Example'
Directory created.
Step
2:
CREATE
OR REPLACE PROCEDURE tes_utl_file
IS
Inputfile UTL_FILE.file_type;
Outputfile UTL_FILE.file_type;
newline VARCHAR2 (5000);
x PLS_INTEGER
:= 0;
sflag BOOLEAN := TRUE;
BEGIN
-- open inputfile to read
Inputfile := UTL_FILE.fopen (‘ExampleDir1’, 'infile.txt', 'r'); -- open
outputfile to write
Outputfile := UTL_FILE.fopen (' ExampleDir1', 'outfile.txt', 'w'); -- if the
file to read was opened
IF UTL_FILE.is_open (Inputfile)
THEN
-- loop through each line in the file
LOOP
BEGIN
UTL_FILE.get_line (Inputfile, newline); --newline is the output buffer
x := UTL_FILE.fgetpos (Inputfile); -- x stores the relative offset position
for an i/p, in bytes.
DBMS_OUTPUT.put_line (TO_CHAR
(x)); --prints the value of x
UTL_FILE.put_line (Outputfile, newline, FALSE); --puts the data in newline
buffer into o/p
UTL_FILE.fflush (Outputfile); --forces the data in buffer to outputfile
IF sflag = TRUE
THEN
UTL_FILE.fseek (Inputfile, NULL, -20); -- adjusts file ptr in back direction
i/p by 20 bytes.
sflag := FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
UTL_FILE.fclose
(Inputfile);
--closing the inputfile
UTL_FILE.fclose
(Outputfile);
--closing the outputfile
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20088, 'Error in Utl_file package');
END;
