5 Replies Latest reply on May 10, 2013 7:30 AM by William Robertson

    UTL file exception handling oracle 11g

    user4485803
      We use oracle 11g
      We use UTL file and exception handling in many place. Thanks in advance.

      We have many utl program and we are writing same exception handling code ,copy and paste .
      It is possible to create new UTL exception procedure and call it.
      I am not sure how to write generic UTL exception procedure and reuse the same.
      I am learning oracle etl files method.
      Please advise.


      sample program 1 :

      DECLARE
      fileHandler UTL_FILE.FILE_TYPE;
      BEGIN
      fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
      UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
      UTL_FILE.FCLOSE(fileHandler);
      EXCEPTION
      when utl_file.invalid_path then
      raise_application_error(-20001,
      'INVALID_PATH: File location or filename was invalid.');
      when utl_file.invalid_mode then
      raise_application_error(-20002,
      'INVALID_MODE: The open_mode parameter in FOPEN was invalid.');
      when utl_file.invalid_filehandle then
      raise_application_error(-20002,
      'INVALID_FILEHANDLE: The file handle was invalid.');
      when utl_file.invalid_operation then
      raise_application_error(-20003,
      'INVALID_OPERATION: The file could not be opened or operated on as requested.');
      when utl_file.read_error then
      raise_application_error(-20004,
      'READ_ERROR: An operating system error occurred during the read operation.');
      when utl_file.write_error then
      raise_application_error(-20005,
      'WRITE_ERROR: An operating system error occurred during the write operation.');
      when utl_file.internal_error then
      raise_application_error(-20006,
      'INTERNAL_ERROR: An unspecified error in PL/SQL.');
      when utl_file.invalid_filename then
      raise_application_error(-20010, 'The filename parameter is invalid.');
      WHEN OTHERS THEN
      IF UTL_FILE.IS_OPEN(fileHandler ) THEN
      UTL_FILE.FCLOSE (fileHandler );
      END IF;
      RAISE;
      END;
      /


      How to write generic procedure of utl exception handling ?
      please advise.


      create or replace procedure sp_utl_exception

      begin
      when utl_file.invalid_path then
      raise_application_error(-20001,
      'INVALID_PATH: File location or filename was invalid.');
      when utl_file.invalid_mode then
      raise_application_error(-20002,
      'INVALID_MODE: The open_mode parameter in FOPEN was invalid.');
      when utl_file.invalid_filehandle then
      raise_application_error(-20002,
      'INVALID_FILEHANDLE: The file handle was invalid.');
      when utl_file.invalid_operation then
      raise_application_error(-20003,
      'INVALID_OPERATION: The file could not be opened or operated on as requested.');
      when utl_file.read_error then
      raise_application_error(-20004,
      'READ_ERROR: An operating system error occurred during the read operation.');
      when utl_file.write_error then
      raise_application_error(-20005,
      'WRITE_ERROR: An operating system error occurred during the write operation.');
      when utl_file.internal_error then
      raise_application_error(-20006,
      'INTERNAL_ERROR: An unspecified error in PL/SQL.');
      when utl_file.invalid_filename then
      raise_application_error(-20010, 'The filename parameter is invalid.');
      WHEN OTHERS THEN
      IF UTL_FILE.IS_OPEN(fileHandler ) THEN
      UTL_FILE.FCLOSE (fileHandler );
      END IF;
      RAISE;
      end;
        • 1. Re: UTL file exception handling oracle 11g
          MaheshKaila
          Hello,

          Common procedure to log exception in log file
          create or replace procedure sp_utl_exception (log_dir varchar2, log_file varchar2, exception_msg varchar2)
          is
             hnd_file   UTL_FILE.file_type;
          begin
             hnd_file := UTL_FILE.fopen (log_dir, log_file, 'A');
             UTL_FILE.put_line (hnd_file, exception_msg);
             UTL_FILE.fclose (hnd_file);
          exception
             when others
             then
                raise;
          end;
          Sample block to call when exception occurs
          DECLARE
             fileHandler   UTL_FILE.FILE_TYPE;
          BEGIN
             fileHandler := UTL_FILE.FOPEN ('test_dir', 'test_file.txt', 'W');
             UTL_FILE.PUTF (fileHandler, 'Writing TO a file\n');
             UTL_FILE.FCLOSE (fileHandler);
          EXCEPTION
             when others
             then
                sp_utl_exception ('log_dir', 'log_file', SQLERRM);
          END;
          /
          Regards,
          Mahesh Kaila
          • 2. Re: UTL file exception handling oracle 11g
            user4485803
            thank you so much for your email.
            Is it possible to define user-defined exception like 'INVALID_PATH: File location or filename was invalid in the procedure?
            Please advise. thanks in advance

            create or replace procedure sp_utl_exception (log_dir varchar2, log_file varchar2, exception_msg varchar2)
            is
            hnd_file UTL_FILE.file_type;
            begin
            hnd_file := UTL_FILE.fopen (log_dir, log_file, 'A');
            UTL_FILE.put_line (hnd_file, exception_msg);
            UTL_FILE.fclose (hnd_file);
            exception
            when others
            then
            raise;
            end;

            Sample block to call when exception occurs

            DECLARE
            fileHandler UTL_FILE.FILE_TYPE;
            BEGIN
            fileHandler := UTL_FILE.FOPEN ('test_dir', 'test_file.txt', 'W');
            UTL_FILE.PUTF (fileHandler, 'Writing TO a file\n');
            UTL_FILE.FCLOSE (fileHandler);
            EXCEPTION
            when others
            then
            sp_utl_exception ('log_dir', 'log_file', SQLERRM);
            END;
            /
            • 3. Re: UTL file exception handling oracle 11g
              William Robertson
              You have one exception block covering multiple file operations, so it will be unclear which operation raised the exception, especially since the RAISE_APPLICATION_ERROR calls are missing the third parameter (TRUE) that would retain the error stack. It would be better to give each step its own exception handler covering only the exceptions that operation is documented as raising.

              The messages do not mention the filename or directory path. For example, 'INVALID_PATH: File location or filename was invalid' would be better if it could report the actual file location or filename used, as well as the operation (open, write, close) that was attempted.
              • 4. Re: UTL file exception handling oracle 11g
                BluShadow
                Mahesh Kaila wrote:
                Hello,

                Common procedure to log exception in log file
                create or replace procedure sp_utl_exception (log_dir varchar2, log_file varchar2, exception_msg varchar2)
                is
                hnd_file   UTL_FILE.file_type;
                begin
                hnd_file := UTL_FILE.fopen (log_dir, log_file, 'A');
                UTL_FILE.put_line (hnd_file, exception_msg);
                UTL_FILE.fclose (hnd_file);
                exception
                when others
                then
                raise;
                end;
                Very poor implementation.

                a) Absolutely no need for that exception handler in there. It should be removed.
                b) As it's a procedure for logging exceptions relating to UTL_FILE, it would seem error prone to be logging the errors with UTL_FILE. For example, what is it supposed to do if the exception is raised because of lack of disk space in those file locations? How is it going to write out the exception with the disk full? Also, if the exception handler is used by multiple processes, then only 1 process at a time can access the log file to write it's exceptions, so it doesn't scale well. Better logging is done by having an autonomous transaction procedure that writes log/trace messages to dedicated table(s). That also means that the logs etc. can be viewed, as appropriate, from any client using SQL (either manually or through a application written to view logs etc.), rather than requiring physical/remote access to the server o/s to go and view the contents of the file, which in itself could lock the file and prevent any process from writing further logs whilst it's being used.
                • 5. Re: UTL file exception handling oracle 11g
                  William Robertson
                  Also it contains no timestamp or information about the procedure that failed, and since error stacks contain multiple lines it will be very hard to get useful information out of it.