xfile.pkg

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE xfile
/*
|| Xtra FILE access from PL/SQL
||
|| More complete file management capabilities for PL/SQL
||
|| Items to add:
||    Generate a unique file number following a certain
||    pattern in a directory.
||
|| Author: Steven Feuerstein
||   Date: 2/26/99
*/
IS 
   c_dirdelim   CONSTANT CHAR (1) := '|';
   /* 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   PLVtab.vc2000_table,
      delim   IN       VARCHAR2 := c_dirdelim
   );

   PROCEDURE getdircontents (
      dir          IN       VARCHAR2,
      filter       IN       VARCHAR2,
      files        IN OUT   PLVtab.vc2000_table,
      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 UTL_FILE.file_type;

   FUNCTION fopen (
      location       IN   VARCHAR2,
      filename       IN   VARCHAR2,
      open_mode      IN   VARCHAR2,
      max_linesize   IN   BINARY_INTEGER
   )
      RETURN UTL_FILE.file_type;

   PROCEDURE fclose (FILE IN OUT UTL_FILE.file_type);

   PROCEDURE fclose_all;

   PROCEDURE fflush (FILE IN UTL_FILE.file_type);

   PROCEDURE get_line (FILE IN UTL_FILE.file_type, buffer OUT VARCHAR2);

   PROCEDURE get_line (
      FILE     IN       UTL_FILE.file_type,
      buffer   OUT      VARCHAR2,
      eof      OUT      BOOLEAN
   );

   PROCEDURE put (FILE IN UTL_FILE.file_type, buffer IN VARCHAR2);

   PROCEDURE new_line (FILE IN UTL_FILE.file_type, lines IN NATURAL := 1);

   PROCEDURE put_line (FILE IN UTL_FILE.file_type, buffer IN VARCHAR2);

   PROCEDURE putf (
      FILE     IN   UTL_FILE.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;
/

CREATE OR REPLACE PACKAGE BODY xfile
IS 
   /* Used to handle conversion from number to boolean. */
   c_true    CONSTANT INTEGER := 1;
   c_false   CONSTANT INTEGER := 0;

   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) = c_true;
   END;

   FUNCTION canwrite (FILE IN VARCHAR2)
      RETURN BOOLEAN
   AS
   BEGIN
      RETURN icanwrite (FILE) = c_true;
   END;

   FUNCTION EXISTS (FILE IN VARCHAR2)
      RETURN BOOLEAN
   AS
   BEGIN
      RETURN iexists (FILE) = c_true;
   END;

   FUNCTION isdirectory (FILE IN VARCHAR2)
      RETURN BOOLEAN
   AS
   BEGIN
      RETURN iisdirectory (FILE) = c_true;
   END;

   FUNCTION isfile (FILE IN VARCHAR2)
      RETURN BOOLEAN
   AS
   BEGIN
      RETURN iisfile (FILE) = c_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 getdircontents (
      dir     IN       VARCHAR2,
      files   IN OUT   PLVtab.vc2000_table,
      delim   IN       VARCHAR2 := c_dirdelim
   )
   IS 
      filenum   PLS_INTEGER;
   BEGIN
      PLVprs.STRING (
         dircontents (dir, delim),
         files,
         filenum,
         delimiters_in      => delim,
         type_in            => PLVprs.c_word
      );
   END;

   PROCEDURE getdircontents (
      dir          IN       VARCHAR2,
      filter       IN       VARCHAR2,
      files        IN OUT   PLVtab.vc2000_table,
      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
      PLVprs.STRING (
         dircontents (dir, delim),
         files,
         filenum,
         delimiters_in      => delim,
         type_in            => PLVprs.c_word
      );
      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   PLVtab.vc2000_table;
      filenum    PLS_INTEGER;
   BEGIN
      getdircontents (dir, filter, filestab, match_case, delim);
      filenum := filestab.FIRST;

      LOOP
         EXIT WHEN filenum IS NULL;
         do.pl /* do.pkg */ (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) = c_true;
   EXCEPTION
      WHEN OTHERS
      THEN
         do.pl /* do.pkg */ ('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   PLVtab.vc2000_table;
      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
            do.pl /* do.pkg */ ('Deleted ' || v_file);
         ELSIF show_deletes AND NOT deleted
         THEN
            do.pl /* do.pkg */ ('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) = c_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) = c_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     PLVtab.vc2000_table;
      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 fopen (
      location    IN   VARCHAR2,
      filename    IN   VARCHAR2,
      open_mode   IN   VARCHAR2
   )
      RETURN UTL_FILE.file_type
   IS
   BEGIN
      RETURN 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 UTL_FILE.file_type
   IS
   BEGIN
      RETURN UTL_FILE.fopen (location, filename, open_mode, max_linesize);
   END;

   PROCEDURE fclose (FILE IN OUT UTL_FILE.file_type)
   IS
   BEGIN
      UTL_FILE.fclose (FILE);
   END;

   PROCEDURE fclose_all
   IS
   BEGIN
      UTL_FILE.fclose_all;
   END;

   PROCEDURE fflush (FILE IN UTL_FILE.file_type)
   IS
   BEGIN
      UTL_FILE.fflush (FILE);
   END;

   PROCEDURE get_line (FILE IN UTL_FILE.file_type, buffer OUT VARCHAR2)
   IS
   BEGIN
      UTL_FILE.get_line (FILE, buffer);
   END;

   PROCEDURE get_line (
      FILE     IN       UTL_FILE.file_type,
      buffer   OUT      VARCHAR2,
      eof      OUT      BOOLEAN
   )
   IS
   BEGIN
      UTL_FILE.get_line (FILE, buffer);
      eof := FALSE ;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         buffer := NULL;
         eof := TRUE ;
   END;

   PROCEDURE put (FILE IN UTL_FILE.file_type, buffer IN VARCHAR2)
   IS
   BEGIN
      UTL_FILE.put (FILE, buffer);
   END;

   PROCEDURE new_line (FILE IN UTL_FILE.file_type, lines IN NATURAL := 1)
   IS
   BEGIN
      UTL_FILE.new_line (FILE, lines);
   END;

   PROCEDURE put_line (FILE IN UTL_FILE.file_type, buffer IN VARCHAR2)
   IS
   BEGIN
      UTL_FILE.put_line (FILE, buffer);
   END;

   PROCEDURE putf (
      FILE     IN   UTL_FILE.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 (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;
END;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/