Sample UTL_FILE Package Scripts

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;