CREATE OR REPLACE PACKAGE fileIO
IS
   c_delim CHAR(1) := ';';
   TYPE dirs_tabtype IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
   dirs dirs_tabtype;
   
   PROCEDURE setpath (str IN VARCHAR2, delim IN VARCHAR2 := c_delim);
   FUNCTION path RETURN VARCHAR2;
   FUNCTION pathlist RETURN dirs_tabtype;

   FUNCTION open (file IN VARCHAR2, loc IN VARCHAR2 := NULL) RETURN UTL_FILE.FILE_TYPE;
   
   PROCEDURE showpath;
   
   PROCEDURE dbg;
   PROCEDURE nodbg;
   FUNCTION debugging RETURN BOOLEAN;
END;
/

CREATE OR REPLACE PACKAGE BODY fileIO
IS
   g_path VARCHAR2(32767);
   g_delim CHAR(1) := c_delim;
   g_dbg BOOLEAN := FALSE;
    
   PROCEDURE dbg IS BEGIN g_dbg := TRUE; END;
   PROCEDURE nodbg IS BEGIN g_dbg := FALSE; END;
   FUNCTION debugging RETURN BOOLEAN IS BEGIN RETURN g_dbg; END;

   PROCEDURE setpath (str IN VARCHAR2, delim IN VARCHAR2 := c_delim)
   IS
     v_loc PLS_INTEGER;
     v_startloc PLS_INTEGER := 1;
     v_item VARCHAR2(2000);
   BEGIN 
      g_path := str;
      g_delim := NVL (delim, c_delim);
      
      dirs.DELETE;
     
      IF str IS NOT NULL 
      THEN 
         IF debugging THEN DBMS_OUTPUT.PUT_LINE ('Parse path ' || str); END IF;
         
         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;
            
            IF debugging
            THEN
               DBMS_OUTPUT.PUT_LINE (
                  'Start at ' || v_startloc || 
                  ' end at ' || v_loc || ' = ' || 
                  v_item);
            END IF;
            
            dirs(NVL (dirs.LAST, 0) + 1) := v_item;

            IF v_loc = 0
            THEN
               EXIT;
            ELSE 
               v_startloc := v_loc + 1;
            END IF;
         END LOOP;
      END IF;
   END;

   FUNCTION path RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_path;
   END;

   FUNCTION pathlist RETURN dirs_tabtype
   IS
   BEGIN
      RETURN dirs;
   END;
   
   FUNCTION open (file IN VARCHAR2, loc IN VARCHAR2 := NULL) RETURN UTL_FILE.FILE_TYPE
   IS
      indx PLS_INTEGER;
      v_dir VARCHAR2(500);
      retval UTL_FILE.FILE_TYPE;
      
      PROCEDURE init_dir IS
      BEGIN
         IF loc IS NOT NULL
         THEN
            indx := NULL;
            v_dir := loc;
         ELSE
            indx := dirs.FIRST;
            v_dir := dirs(indx);
         END IF;
      END;
      
      PROCEDURE set_next_dir IS
      BEGIN
         indx := dirs.NEXT (indx);
         IF indx IS NULL
         THEN
            v_dir := NULL;
         ELSE
            v_dir := dirs(indx);
         END IF;
      END;
      
   BEGIN 
      IF debugging THEN DBMS_OUTPUT.PUT_LINE ('Open ' || file); END IF;
      
      init_dir;
           
      WHILE v_dir IS NOT NULL
      LOOP
         BEGIN
            IF debugging THEN DBMS_OUTPUT.PUT_LINE ('...looking in ' || v_dir); END IF;
            
            retval := UTL_FILE.FOPEN (v_dir, file, 'R');
            EXIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               set_next_dir;
         END;
      END LOOP;
      RETURN retval;
   END;
   
   PROCEDURE showpath
   IS
      indx PLS_INTEGER := dirs.FIRST;
   BEGIN 
      WHILE indx IS NOT NULL
      LOOP
         DBMS_OUTPUT.PUT_LINE (dirs(indx));
         indx := dirs.NEXT (indx);
      END LOOP;
   END;
END;
/

SHO ERR


/*======================================================================
| 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/
*/