Extending UTL_FILE with Java Methods

View All Scripts Login to Run Script

Script Name Extending UTL_FILE with Java Methods
Description UTL_FILE is a handy package but it doesn't do everything everyone wants with files (such as: get list of files in directory). So I "fell back" on Java to build out file management via a PL/SQL API. NOTE: this code will NOT execute in LiveSQL, and it is definitely offered on a USER BE CAREFUL basis. You need all the appropriate privileges and you need to carefully review the code offered here before you use it anywhere NEAR to production.
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday July 14, 2016

Statement 1

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JFile" AS 
import java.io.File; 
public class JFile { 
// Author: Steven Feuerstein 
 
   // Notice that the actual values don''t matter one bit.  
   public static int tVal () { return 10009; };    
   public static int fVal () { return -18703; };  
    
   public static String separator (String fileName) { 
      File myFile = new File (fileName); 
      return myFile.separator; 
      } 
 
   /* Also from Paul Sharples 
   public static String separator () { 
      return System.getProperty("file.separator"); 
   } 
   */ 
    
   public static int canRead (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.canRead(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static int canWrite (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.canWrite(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static int exists (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.exists(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static int isDirectory (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.isDirectory(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static int isFile (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.isFile(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static long length (String fileName) { 
      File myFile = new File (fileName); 
      return myFile.length(); 
      } 
 
   public static String parentDir (String fileName) { 
      File myFile = new File (fileName); 
      return myFile.getParent(); 
      } 
 
   public static String pathName (String fileName) { 
      File myFile = new File (fileName); 
      return myFile.getPath(); 
      } 
 
   public static long lastModified (String fileName) { 
      File myFile = new File (fileName); 
      return myFile.lastModified(); 
      } 
 
   public static int delete (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.delete(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static int mkdir (String fileName) { 
      File myFile = new File (fileName); 
      boolean retval = myFile.mkdir(); 
      if (retval) return tVal(); else return fVal(); 
      } 
 
   public static int rename ( 
         String oldFile, String newFile) { 
      File myFile = new File (oldFile); 
      File myFile2 = new File (newFile); 
      boolean retval = myFile.renameTo(myFile2); 
      if (retval) return tVal(); else return fVal(); 
      } 
       
   /* 
   public static oracle.sql.ARRAY dirContents (String dir)  
      throws SQLException, ClassNotFoundException { 
       
      File myDir = new File (dir); 
      String[] filesList = myDir.list(); 
       
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
      Connection con = new OracleDriver().defaultConnection(); 
       
      ArrayDescriptor arrayDesc =  
         ArrayDescriptor.createDescriptor ("directory_t", con); 
      oracle.sql.ARRAY filesArray = new oracle.sql.ARRAY (arrayDesc, con, filesList); 
       
      return filesArray; 
      } 
   */ 
 
   // StringBuffer implementation from Tom Berthoff 
   public static String dirContents (String dir, String delim) { 
      File myDir = new File (dir); 
      String[] filesList = myDir.list(); 
      StringBuffer contents = new StringBuffer(); 
      for (int i = 0; i < filesList.length; i++) { 
          contents.append( delim ); 
          contents.append( filesList[i] ); 
      }       
         //contents = contents + delim + filesList[i]; // Utrecht 4/2002 
      return contents.toString(); 
   }       
   public static void main (String[] args) { 
      String contents = dirContents (args[0], args[1]); 
      System.out.println (contents); 
      /* 
      try { 
         oracle.sql.ARRAY filesArray = dirContents (args[0]); 
         String[] files = (String[]) filesArray.getArray (); 
         for (int i = 0; i <= files.length; i++)  
            System.out.println (files[i]); 
         } 
      catch ( SQLException e ) { 
         System.out.println (e.getMessage());} 
      catch ( ClassNotFoundException e ) { 
         System.out.println ("ClassNotFoundException");} 
      */ 
   }             
}

ORA-04088: error during execution of trigger 'SYS.DBCLOUD_BEFORE_DDL_DB_TRG' ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 1802
ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 2011
ORA-06512: at line 2

More Details: https://docs.oracle.com/error-help/db/ora-04088

Statement 2

CREATE OR REPLACE PACKAGE xfile 
/* 
|| xfile: Xtra FILE access from PL/SQL 
|| 
|| More complete file management capabilities for PL/SQL 
|| 
|| You can install this package and then do a global search 
|| and replace of "UTL_FILE" to "xfile", and then switch 
|| over to xfile for all future file IO activities in PL/SQL. 
|| 
|| Author: Steven Feuerstein 
|| 
|| Modification History: 
||   Date        Who         What 
|| Oct 3 2007    SF          Add file_type record type to provide full 
||                           compatibility with UTL_FILE. 
|| Feb 26 1999   SF          Create package. 
*/ 
IS 
   c_dirdelim   CONSTANT CHAR (1) := '|'; 
 
   SUBTYPE maxvc2 IS VARCHAR2 (32767); 
 
   -- List of file names 
   TYPE list_aat IS TABLE OF maxvc2 
      INDEX BY BINARY_INTEGER; 
 
   /* Record that mimics UTL_FILE file handle record. 
      WARNING! Must check each new version of Oracle 
      to keep this in synch. 
   */ 
   TYPE file_type IS RECORD ( 
      ID          BINARY_INTEGER 
    , datatype    BINARY_INTEGER 
    , byte_mode   BOOLEAN 
   ); 
 
   /* Status of file */ 
   FUNCTION canread (FILE IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   FUNCTION canwrite (FILE IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   FUNCTION EXISTS (FILE IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   FUNCTION isdirectory (FILE IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   FUNCTION isfile (FILE IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   FUNCTION separator (FILE IN VARCHAR2) 
      RETURN VARCHAR2; 
 
   /* Information about file */ 
   FUNCTION LENGTH (FILE IN VARCHAR2) 
      RETURN NUMBER; 
 
   FUNCTION parentdir (FILE IN VARCHAR2) 
      RETURN VARCHAR2; 
 
   FUNCTION pathname (FILE IN VARCHAR2) 
      RETURN VARCHAR2; 
 
   FUNCTION lastmodified (FILE IN VARCHAR2) 
      RETURN NUMBER; 
 
   /* Not a date; only useful for comparisions. */ 
   FUNCTION dircontents (dir IN VARCHAR2, delim IN VARCHAR2) 
      RETURN VARCHAR2; 
 
   PROCEDURE getdircontents ( 
      dir IN VARCHAR2 
    , files IN OUT VARCHAR2 
    , delim IN VARCHAR2 := c_dirdelim 
   ); 
 
   PROCEDURE getdircontents ( 
      dir IN VARCHAR2 
    , files IN OUT list_aat 
    , delim IN VARCHAR2 := c_dirdelim 
   ); 
 
   PROCEDURE getdircontents ( 
      dir IN VARCHAR2 
    , filter IN VARCHAR2 
    , files IN OUT list_aat 
    , match_case IN BOOLEAN := TRUE 
    , delim IN VARCHAR2 := c_dirdelim 
   ); 
 
   PROCEDURE showdircontents ( 
      dir IN VARCHAR2 
    , filter IN VARCHAR2 
    , match_case IN BOOLEAN := TRUE 
    , delim IN VARCHAR2 := c_dirdelim 
   ); 
 
   FUNCTION DELETE (FILE IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   PROCEDURE DELETE ( 
      dir IN VARCHAR2 
    , FILE IN VARCHAR2 := NULL 
    , match_case IN BOOLEAN := TRUE 
    , show_deletes IN BOOLEAN := FALSE 
   ); 
 
   FUNCTION mkdir (dir IN VARCHAR2) 
      RETURN BOOLEAN; 
 
   FUNCTION RENAME ( 
      oldfile IN VARCHAR2 
    , newfile IN VARCHAR2 
    , showme IN BOOLEAN := FALSE 
   ) 
      RETURN BOOLEAN; 
 
   PROCEDURE chgext ( 
      dir IN VARCHAR2 
    , oldext IN VARCHAR2 
    , newext IN VARCHAR2 
    , filter IN VARCHAR2 := '%' 
    , showonly IN BOOLEAN := FALSE 
   ); 
 
   /* UTL_FILE compatibility operations */ 
   FUNCTION fopen ( 
      LOCATION IN VARCHAR2 
    , filename IN VARCHAR2 
    , open_mode IN VARCHAR2 
   ) 
      RETURN file_type; 
 
   FUNCTION fopen ( 
      LOCATION IN VARCHAR2 
    , filename IN VARCHAR2 
    , open_mode IN VARCHAR2 
    , max_linesize IN BINARY_INTEGER 
   ) 
      RETURN file_type; 
 
   PROCEDURE fclose (FILE IN OUT file_type); 
 
   PROCEDURE fclose_all; 
 
   PROCEDURE fflush (FILE IN file_type); 
 
   PROCEDURE get_line (FILE IN file_type, buffer OUT VARCHAR2); 
 
   PROCEDURE get_line (FILE IN file_type, buffer OUT VARCHAR2, eof OUT BOOLEAN); 
 
   PROCEDURE put (FILE IN file_type, buffer IN VARCHAR2); 
 
   PROCEDURE new_line (FILE IN file_type, lines IN NATURAL := 1); 
 
   PROCEDURE put_line (FILE IN file_type, buffer IN VARCHAR2); 
 
   PROCEDURE putf ( 
      FILE IN file_type 
    , format IN VARCHAR2 
    , arg1 IN VARCHAR2 DEFAULT NULL 
    , arg2 IN VARCHAR2 DEFAULT NULL 
    , arg3 IN VARCHAR2 DEFAULT NULL 
    , arg4 IN VARCHAR2 DEFAULT NULL 
    , arg5 IN VARCHAR2 DEFAULT NULL 
   ); 
 
   FUNCTION loblength (dir IN VARCHAR2, FILE IN VARCHAR2) 
      RETURN NUMBER; 
END;

Package created.

Statement 3

CREATE OR REPLACE PACKAGE BODY xfile 
IS 
   /* Used to handle conversion from number to boolean. */ 
   g_true    INTEGER; 
   g_false   INTEGER; 
 
   FUNCTION tval 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.tVal () return int'; 
 
   FUNCTION fval 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.fVal () return int'; 
 
   FUNCTION separator (FILE IN VARCHAR2) 
      RETURN VARCHAR2 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.separator (java.lang.String) return java.lang.String'; 
 
   FUNCTION icanread (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.canRead (java.lang.String) return int'; 
 
   FUNCTION icanwrite (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.canWrite (java.lang.String) return int'; 
 
   FUNCTION iexists (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.exists (java.lang.String) return int'; 
 
   FUNCTION iisdirectory (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.isDirectory (java.lang.String) return int'; 
 
   FUNCTION iisfile (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.isFile (java.lang.String) return int'; 
 
--------------------- 
   FUNCTION canread (FILE IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN icanread (FILE) = g_true; 
   END; 
 
   FUNCTION canwrite (FILE IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN icanwrite (FILE) = g_true; 
   END; 
 
   FUNCTION EXISTS (FILE IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN iexists (FILE) = g_true; 
   END; 
 
   FUNCTION isdirectory (FILE IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN iisdirectory (FILE) = g_true; 
   END; 
 
   FUNCTION isfile (FILE IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN iisfile (FILE) = g_true; 
   END; 
 
--------------------- 
   FUNCTION LENGTH (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.length (java.lang.String) return long'; 
 
   FUNCTION parentdir (FILE IN VARCHAR2) 
      RETURN VARCHAR2 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.parentDir (java.lang.String) return java.lang.String'; 
 
   FUNCTION pathname (FILE IN VARCHAR2) 
      RETURN VARCHAR2 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.pathName (java.lang.String) return java.lang.String'; 
 
   FUNCTION lastmodified (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.lastModified (java.lang.String) return long'; 
 
   FUNCTION dircontents (dir IN VARCHAR2, delim IN VARCHAR2) 
      RETURN VARCHAR2 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.dirContents (java.lang.String, java.lang.String) return java.lang.String'; 
 
   PROCEDURE getdircontents ( 
      dir IN VARCHAR2 
    , files IN OUT VARCHAR2 
    , delim IN VARCHAR2 := c_dirdelim 
   ) 
   IS 
   BEGIN 
      files := dircontents (dir, delim); 
   END; 
 
   PROCEDURE list_to_coll ( 
      str IN VARCHAR2 
    , list_inout IN OUT list_aat 
    , delim IN VARCHAR2 DEFAULT ',' 
    , append_to_list_in IN BOOLEAN DEFAULT TRUE 
   ) 
   IS 
      v_loc        PLS_INTEGER; 
      v_row        PLS_INTEGER; 
      v_startloc   PLS_INTEGER := 1; 
      v_item       maxvc2; 
   BEGIN 
      IF append_to_list_in 
      THEN 
         v_row := NVL (list_inout.LAST, 0) + 1; 
      ELSE 
         list_inout.DELETE; 
         v_row := 1; 
      END IF; 
 
      IF str IS NOT NULL 
      THEN 
         LOOP 
            v_loc := INSTR (str, delim, v_startloc); 
 
            IF v_loc = v_startloc 
            THEN 
               v_item := NULL; 
            ELSIF v_loc = 0 
            THEN 
               v_item := SUBSTR (str, v_startloc); 
            ELSE 
               v_item := SUBSTR (str, v_startloc, v_loc - v_startloc); 
            END IF; 
 
            list_inout (v_row) := v_item; 
 
            IF v_loc = 0 
            THEN 
               EXIT; 
            ELSE 
               v_startloc := v_loc + 1; 
               v_row := v_row + 1; 
            END IF; 
         END LOOP; 
      END IF; 
   END list_to_coll; 
 
   PROCEDURE getdircontents ( 
      dir IN VARCHAR2 
    , files IN OUT list_aat 
    , delim IN VARCHAR2 := c_dirdelim 
   ) 
   IS 
      filenum   PLS_INTEGER; 
   BEGIN 
      list_to_coll (dir, files, delim, append_to_list_in => FALSE); 
   END; 
 
   PROCEDURE getdircontents ( 
      dir IN VARCHAR2 
    , filter IN VARCHAR2 
    , files IN OUT list_aat 
    , match_case IN BOOLEAN := TRUE 
    , delim IN VARCHAR2 := c_dirdelim 
   ) 
   IS 
      filenum   PLS_INTEGER; 
 
      FUNCTION MATCHED (file1 IN VARCHAR2, file2 IN VARCHAR2) 
         RETURN BOOLEAN 
      IS 
         retval   BOOLEAN := FALSE; 
      BEGIN 
         IF match_case 
         THEN 
            retval := file1 LIKE file2; 
         ELSE 
            retval := LOWER (file1) LIKE LOWER (file2); 
         END IF; 
 
         RETURN retval; 
      END; 
   BEGIN 
      list_to_coll (dircontents (dir, delim) 
                  , files 
                  , delim 
                  , append_to_list_in      => FALSE 
                   ); 
      filenum := files.FIRST; 
 
      LOOP 
         EXIT WHEN filenum IS NULL; 
 
         IF NOT MATCHED (files (filenum), filter) 
         THEN 
            files.DELETE (filenum); 
         END IF; 
 
         filenum := files.NEXT (filenum); 
      END LOOP; 
   END; 
 
   PROCEDURE showdircontents ( 
      dir IN VARCHAR2 
    , filter IN VARCHAR2 
    , match_case IN BOOLEAN := TRUE 
    , delim IN VARCHAR2 := c_dirdelim 
   ) 
   IS 
      filestab   list_aat; 
      filenum    PLS_INTEGER; 
   BEGIN 
      getdircontents (dir, filter, filestab, match_case, delim); 
      filenum := filestab.FIRST; 
 
      LOOP 
         EXIT WHEN filenum IS NULL; 
         DBMS_OUTPUT.put_line (filestab (filenum)); 
         filenum := filestab.NEXT (filenum); 
      END LOOP; 
   END; 
 
--------------------- 
   FUNCTION idelete (FILE IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.delete (java.lang.String) return int'; 
 
   FUNCTION DELETE (FILE IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN idelete (FILE) = g_true; 
   EXCEPTION 
      WHEN OTHERS 
      THEN 
         DBMS_OUTPUT.put_line ('Error deleting: ' || SQLERRM); 
         RETURN FALSE; 
   END; 
 
   PROCEDURE DELETE ( 
      dir IN VARCHAR2 
    , FILE IN VARCHAR2 := NULL 
    , match_case IN BOOLEAN := TRUE 
    , show_deletes IN BOOLEAN := FALSE 
   ) 
   IS 
      filestab   list_aat; 
      filenum    PLS_INTEGER; 
      deleted    BOOLEAN; 
      v_file     VARCHAR2 (2000); 
   BEGIN 
      getdircontents (dir, FILE, filestab); 
      filenum := filestab.FIRST; 
 
      LOOP 
         EXIT WHEN filenum IS NULL; 
         v_file := dir || separator (filestab (filenum)) 
                   || filestab (filenum); 
         deleted := xfile.DELETE (v_file); 
 
         IF show_deletes AND deleted 
         THEN 
            DBMS_OUTPUT.put_line ('Deleted ' || v_file); 
         ELSIF show_deletes AND NOT deleted 
         THEN 
            DBMS_OUTPUT.put_line ('Unable to delete ' || v_file); 
         END IF; 
 
         filenum := filestab.NEXT (filenum); 
      END LOOP; 
   END; 
 
   FUNCTION imkdir (dir IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.mkdir (java.lang.String) return int'; 
 
   FUNCTION mkdir (dir IN VARCHAR2) 
      RETURN BOOLEAN 
   AS 
   BEGIN 
      RETURN imkdir (dir) = g_true; 
   END; 
 
   FUNCTION irename (oldfile IN VARCHAR2, newfile IN VARCHAR2) 
      RETURN NUMBER 
   AS 
      LANGUAGE JAVA 
      NAME 'JFile.rename (java.lang.String, java.lang.String) return int'; 
 
   FUNCTION RENAME ( 
      oldfile IN VARCHAR2 
    , newfile IN VARCHAR2 
    , showme IN BOOLEAN := FALSE 
   ) 
      RETURN BOOLEAN 
   AS 
      v_bool   BOOLEAN := irename (oldfile, newfile) = g_true; 
   BEGIN 
      IF v_bool AND showme 
      THEN 
         DBMS_OUTPUT.put_line ('Renamed ' || oldfile || ' to ' || newfile); 
      END IF; 
 
      RETURN v_bool; 
   END; 
 
   PROCEDURE chgext ( 
      dir IN VARCHAR2 
    , oldext IN VARCHAR2 
    , newext IN VARCHAR2 
    , filter IN VARCHAR2 := '%' 
    , showonly IN BOOLEAN := FALSE 
   ) 
   IS 
      files     list_aat; 
      filenum   PLS_INTEGER; 
      newfile   VARCHAR2 (200); 
      renamed   BOOLEAN; 
   BEGIN 
      xfile.getdircontents (dir 
                          , filter || '.' || oldext 
                          , files 
                          , match_case      => FALSE 
                           ); 
      filenum := files.FIRST; 
 
      LOOP 
         EXIT WHEN filenum IS NULL; 
         newfile := 
               dir 
            || '\' 
            || SUBSTR (files (filenum), 1, INSTR (files (filenum), '.')) 
            || newext; 
 
         IF showonly 
         THEN 
            DBMS_OUTPUT.put_line ('Change to ' || newfile); 
         ELSE 
            renamed := xfile.RENAME (dir || '\' || files (filenum), newfile); 
         END IF; 
 
         filenum := files.NEXT (filenum); 
      END LOOP; 
   END; 
 
   /* 
      UTL_FILE compatibility operations. 
   */ 
   FUNCTION to_xfile_file_type (file_type_in IN UTL_FILE.file_type) 
      RETURN file_type 
   IS 
      l_return   file_type; 
   BEGIN 
      l_return.ID := file_type_in.ID; 
      l_return.datatype := file_type_in.datatype; 
      l_return.byte_mode := file_type_in.byte_mode; 
      RETURN l_return; 
   END to_xfile_file_type; 
 
   FUNCTION to_utl_file_type (file_type_in IN file_type) 
      RETURN UTL_FILE.file_type 
   IS 
      l_return   UTL_FILE.file_type; 
   BEGIN 
      l_return.ID := file_type_in.ID; 
      l_return.datatype := file_type_in.datatype; 
      l_return.byte_mode := file_type_in.byte_mode; 
      RETURN l_return; 
   END to_utl_file_type; 
 
   FUNCTION fopen ( 
      LOCATION IN VARCHAR2 
    , filename IN VARCHAR2 
    , open_mode IN VARCHAR2 
   ) 
      RETURN file_type 
   IS 
   BEGIN 
      RETURN to_xfile_file_type (UTL_FILE.fopen (LOCATION, filename 
                                               , open_mode) 
                                ); 
   END; 
 
   FUNCTION fopen ( 
      LOCATION IN VARCHAR2 
    , filename IN VARCHAR2 
    , open_mode IN VARCHAR2 
    , max_linesize IN BINARY_INTEGER 
   ) 
      RETURN file_type 
   IS 
   BEGIN 
      RETURN to_xfile_file_type (UTL_FILE.fopen (LOCATION 
                                               , filename 
                                               , open_mode 
                                               , max_linesize 
                                                ) 
                                ); 
   END; 
 
   PROCEDURE fclose (FILE IN OUT file_type) 
   IS 
      l_file   UTL_FILE.file_type := to_utl_file_type (FILE); 
   BEGIN 
      UTL_FILE.fclose (l_file); 
      FILE := to_xfile_file_type (l_file); 
   END; 
 
   PROCEDURE fclose_all 
   IS 
   BEGIN 
      UTL_FILE.fclose_all; 
   END; 
 
   PROCEDURE fflush (FILE IN file_type) 
   IS 
   BEGIN 
      UTL_FILE.fflush (to_utl_file_type (FILE)); 
   END; 
 
   PROCEDURE get_line (FILE IN file_type, buffer OUT VARCHAR2) 
   IS 
   BEGIN 
      UTL_FILE.get_line (to_utl_file_type (FILE), buffer); 
   END; 
 
   PROCEDURE get_line (FILE IN file_type, buffer OUT VARCHAR2, eof OUT BOOLEAN) 
   IS 
   BEGIN 
      UTL_FILE.get_line (to_utl_file_type (FILE), buffer); 
      eof := FALSE; 
   EXCEPTION 
      WHEN NO_DATA_FOUND 
      THEN 
         buffer := NULL; 
         eof := TRUE; 
   END; 
 
   PROCEDURE put (FILE IN file_type, buffer IN VARCHAR2) 
   IS 
   BEGIN 
      UTL_FILE.put (to_utl_file_type (FILE), buffer); 
   END; 
 
   PROCEDURE new_line (FILE IN file_type, lines IN NATURAL := 1) 
   IS 
   BEGIN 
      UTL_FILE.new_line (to_utl_file_type (FILE), lines); 
   END; 
 
   PROCEDURE put_line (FILE IN file_type, buffer IN VARCHAR2) 
   IS 
   BEGIN 
      UTL_FILE.put_line (to_utl_file_type (FILE), buffer); 
   END; 
 
   PROCEDURE putf ( 
      FILE IN file_type 
    , format IN VARCHAR2 
    , arg1 IN VARCHAR2 DEFAULT NULL 
    , arg2 IN VARCHAR2 DEFAULT NULL 
    , arg3 IN VARCHAR2 DEFAULT NULL 
    , arg4 IN VARCHAR2 DEFAULT NULL 
    , arg5 IN VARCHAR2 DEFAULT NULL 
   ) 
   IS 
   BEGIN 
      UTL_FILE.putf (to_utl_file_type (FILE) 
                   , format 
                   , arg1 
                   , arg2 
                   , arg3 
                   , arg4 
                   , arg5 
                    ); 
   END; 
 
   FUNCTION loblength (dir IN VARCHAR2, FILE IN VARCHAR2) 
      RETURN NUMBER 
   IS 
      v_loc   BFILE := BFILENAME (dir, FILE); 
   BEGIN 
      RETURN DBMS_LOB.getlength (v_loc); 
   END; 
BEGIN 
   g_true := tval; 
   g_false := fval; 
END;

Errors: PACKAGE BODY XFILE
Line/Col: 385/4 PL/SQL: Item ignored
Line/Col: 385/49 PLS-00201: identifier 'UTL_FILE' must be declared
Line/Col: 396/4 PL/SQL: Item ignored
Line/Col: 397/14 PLS-00201: identifier 'UTL_FILE' must be declared
Line/Col: 415/7 PL/SQL: Statement ignored
Line/Col: 415/34 PLS-00201: identifier 'UTL_FILE' must be declared
Line/Col: 429/7 PL/SQL: Statement ignored
Line/Col: 429/34 PLS-00201: identifier 'UTL_FILE' must be declared
Line/Col: 439/16 PLS-00201: identifier 'UTL_FILE' must be declared
Line/Col: 439/16 PL/SQL: Item ignored
Line/Col: 441/7 PL/SQL: Statement ignored
Line/Col: 441/24 PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line/Col: 442/7 PL/SQL: Statement ignored
Line/Col: 442/35 PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line/Col: 448/7 PLS-00201: identifier 'UTL_FILE' must be declared
Line/Col: 448/7 PL/SQL: Statement ignored
Line/Col: 454/7 PL/SQL: Statement ignored
Line/Col: 454/24 PLS-00313: 'TO_UTL_FILE_TYPE' not declared in this scope
Line/Col: 460/7 PL/SQL: Statement ignored
Line/Col: 460/26 PLS-00313: 'TO_UTL_FILE_TYPE' not declared in this scope

More Details: https://docs.oracle.com/error-help/db/ora-24344

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database