testfile.sp

Oracle PL/SQL procedure generator (second generator type)

create or replace PROCEDURE testplg (loc IN VARCHAR2)
IS
   nm VARCHAR2(200) := 'teprep.gdr';
   line VARCHAR2(2000);
   
   fid UTL_FILE.FILE_TYPE;

   PROCEDURE disperr (
      str IN VARCHAR2 := NULL)
   IS
   BEGIN
      IF str IS NULL
      THEN
         DBMS_OUTPUT.PUT_LINE (
            'Error ' || SQLERRM || 
            ' writing to location ' || loc || 
            ' and filename ' || nm
            );
      ELSE
         DBMS_OUTPUT.PUT_LINE (
            str || ' with location ' || loc || 
                   ' and filename ' || nm
            );
      END IF;
      UTL_FILE.FCLOSE (fid);
   END;

BEGIN
   DBMS_OUTPUT.ENABLE (1000000);

   fid := UTL_FILE.FOPEN (loc, nm, 'R');

   LOOP
      UTL_FILE.GET_LINE (fid, line);
      DBMS_OUTPUT.PUT_LINE(line);
   END LOOP;

   UTL_FILE.FCLOSE (fid);

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      UTL_FILE.FCLOSE (fid);

   WHEN UTL_FILE.INVALID_PATH
   THEN   
       disperr ('UTL_FILE Invalid path');
   
   WHEN UTL_FILE.INVALID_MODE
   THEN
       disperr ('UTL_FILE Invalid mode');

   WHEN UTL_FILE.INVALID_FILEHANDLE
   THEN
       disperr ('UTL_FILE Invalid filehandle');

   WHEN UTL_FILE.INVALID_OPERATION
   THEN
       disperr ('UTL_FILE Invalid operation');

   WHEN UTL_FILE.READ_ERROR
   THEN
       disperr ('UTL_FILE Read error');

   WHEN UTL_FILE.WRITE_ERROR
   THEN
       disperr ('UTL_FILE Write error');

   WHEN UTL_FILE.INTERNAL_ERROR
   THEN
       disperr ('UTL_FILE Internal error');

   WHEN OTHERS
   THEN
       disperr;
END;