File Handling using PL/SQL - UTL_FILE

Intention of this post is to list out different exceptions being raised while using UTL_FILE.

First create DBA Directory

File Handling Program -

DECLARE

   p_file_path     VARCHAR2 (200);

   p_file_handle   UTL_FILE.FILE_TYPE;

   p_data_file     VARCHAR2 (200);

BEGIN

   p_data_file := 'SANDEEP_UTLFILE_TEST.txt'; 

   p_file_path := 'SANDEEP_DIR'; 

  
  DBMS_OUTPUT.PUT_LINE('File Path     >>' ||p_file_path );
  DBMS_OUTPUT.PUT_LINE('File Name   >>' ||p_data_file ); 

   p_file_handle := UTL_FILE.FOPEN (p_file_path,p_data_file,'W',32767);// OPEN the FILE

 /********************************************************************************** 

   p_file_path                    >> Location where data file being placed

   p_data_file                    >> Data file name

  ‘W’                               >> Operation Mode  
                                             'W' indicates write mode , 

                                              'R' indicates read mode , 
                                              'A' indicates Append mode

   32767                           >> Length of the string. 32767 is the max length.

 **********************************************************************************/

   UTL_FILE.PUT_LINE (p_file_handle, 'Sandeep is working on UTL File Demo'); 
   // WRITE into FILE
-- UTL_FILE.FFLUSH(p_file_handle); 
-- FFLUSH will write all buffer contents, useful if contents are larger in size.

  UTL_FILE.FCLOSE(p_file_handle);// CLOSE the FILE

EXCEPTION

          WHEN  UTL_FILE.invalid_mode  THEN

            DBMS_OUTPUT.PUT_LINE('20051, Invalid Option in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_path THEN

            DBMS_OUTPUT.PUT_LINE('20052, Invalid Path in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_filehandle THEN

            DBMS_OUTPUT.PUT_LINE('20053, Invalid Filehandle in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_operation THEN

            DBMS_OUTPUT.PUT_LINE('20054, Invalid Operation in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.read_error THEN

            DBMS_OUTPUT.PUT_LINE('20055, Read Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.write_error THEN

            DBMS_OUTPUT.PUT_LINE('20056, Write Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.internal_error THEN

            DBMS_OUTPUT.PUT_LINE('20057, Internal Error in UTL File le >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Unexpected Error occured during report generation >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

END;

OUTPUT – File Generated