filepath1.pkg

CREATE TYPE dirs_tabtype IS TABLE OF VARCHAR2(2000);
/

CREATE OR REPLACE PACKAGE fileIO
IS
   c_delim CHAR(1) := ';';
   
   dirs dirs_tabtype := dirs_tabtype ();
   
   -- Unit test list
   ut_dirs dirs_tabtype := 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;
   
   -- Unit test code in same package
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;
   PROCEDURE ut_setpath;
END;
/

CREATE OR REPLACE PACKAGE BODY fileIO
IS
   g_path VARCHAR2(32767);
   g_delim CHAR(1) := c_delim;

   PROCEDURE setpath (str IN VARCHAR2, delim IN VARCHAR2 := c_delim)
   IS
   BEGIN
      g_path := str;
      g_delim := NVL (delim, c_delim);
      str2list.parse (str, g_delim, 'fileIO', 'dirs');
   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;
      
      /* Location of next path separator */
      v_lastsep PLS_INTEGER := 1;
      v_sep PLS_INTEGER := INSTR (g_path, c_delim);
      v_dir VARCHAR2(500);
      retval UTL_FILE.FILE_TYPE;
      
      PROCEDURE set_next_dir IS
      BEGIN
         v_sep := INSTR (g_path, c_delim, v_sep+1); 
         IF v_sep = 0
         THEN
            v_dir := SUBSTR (g_path, v_lastsep);
         ELSE
            v_dir := SUBSTR (g_path, v_lastsep, v_sep - v_lastsep);
         END IF;
      END;
   BEGIN 
      IF loc IS NOT NULL
      THEN
         indx := NULL;
         v_dir := loc;
      ELSE
         indx := dirs.FIRST;
         v_dir := dirs(indx);
      END IF;
           
      WHILE v_dir IS NOT NULL
      LOOP
         BEGIN
            --DBMS_OUTPUT.PUT_LINE ('...looking in ' || v_dir);
            retval := UTL_FILE.FOPEN (v_dir, file, 'R');
            EXIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               indx := dirs.NEXT (indx);
               IF indx IS NULL
               THEN
                  v_dir := NULL;
               ELSE
                  v_dir := dirs(indx);
               END IF;
         END;
      END LOOP;
      RETURN retval;
   END;
   
   PROCEDURE ut_setup IS
   BEGIN
      NULL:
   END;
   
   PROCEDURE ut_teardown
   IS BEGIN NULL; END;
   
   PROCEDURE ut_setpath
   IS
   BEGIN
      /* Populate base collection */
      ut_dirs.DELETE;
      
      ut_dirs.EXTEND(2);
      ut_dirs(1) := 'c:\temp';
      ut_dirs(2) := 'e:\demo';
      
      /* Call setpath to do the work */
      setpath ('c:\temp;e:\demo');
      
      utAssert.eqColl (
         'Valid double entry',
         'fileio.dirs',
         'fileio.ut_dirs'
         );
   END;
   
END;
/