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.
Example 1:
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:
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;
0 comments