Sample UTL_FILE Package Scripts

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;