How to write to files using utl_file in oracle

Question

How to use put function.my procedure is not compiling with put. but putline is working fine. i want to print in the same line

Answer 1

Here's an example of code which uses the UTL_FILE.PUT and UTL_FILE.PUT_LINE calls:

declare 
  fHandle  UTL_FILE.FILE_TYPE;
begin
  fHandle := UTL_FILE.FOPEN('my_directory', 'test_file', 'w');

  UTL_FILE.PUT(fHandle, 'This is the first line');
  UTL_FILE.PUT(fHandle, 'This is the second line');
  UTL_FILE.PUT_LINE(fHandle, 'This is the third line');

  UTL_FILE.FCLOSE(fHandle);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
    RAISE;
end;

The output from this looks like:

This is the first lineThis is the second lineThis is the third line

Share and enjoy.

Answer 2

Here is a robust function for using UTL_File.putline that includes the necessary error handling. It also handles headers, footers and a few other exceptional cases.

PROCEDURE usp_OUTPUT_ToFileAscii(p_Path IN VARCHAR2, p_FileName IN VARCHAR2, p_Input IN refCursor, p_Header in VARCHAR2, p_Footer IN VARCHAR2, p_WriteMode VARCHAR2) IS

              vLine VARCHAR2(30000);
              vFile UTL_FILE.file_type; 
              vExists boolean;
              vLength number;
              vBlockSize number;
    BEGIN

        UTL_FILE.fgetattr(p_path, p_FileName, vExists, vLength, vBlockSize);

                 FETCH p_Input INTO vLine;
         IF p_input%ROWCOUNT > 0
         THEN
            IF vExists THEN
               vFile := UTL_FILE.FOPEN_NCHAR(p_Path, p_FileName, p_WriteMode);
            ELSE
               --even if the append flag is passed if the file doesn't exist open it with W.
                vFile := UTL_FILE.FOPEN(p_Path, p_FileName, 'W');
            END IF;
            --GET HANDLE TO FILE
            IF p_Header IS NOT NULL THEN 
              UTL_FILE.PUT_LINE(vFile, p_Header);
            END IF;
            UTL_FILE.PUT_LINE(vFile, vLine);
            DBMS_OUTPUT.PUT_LINE('Record count > 0');

             --LOOP THROUGH CURSOR VAR
             LOOP
                FETCH p_Input INTO vLine;

                EXIT WHEN p_Input%NOTFOUND;

                UTL_FILE.PUT_LINE(vFile, vLine);

             END LOOP;


             IF p_Footer IS NOT NULL THEN 
                UTL_FILE.PUT_LINE(vFile, p_Footer);
             END IF;

             CLOSE p_Input;
             UTL_FILE.FCLOSE(vFile);
        ELSE
          DBMS_OUTPUT.PUT_LINE('Record count = 0');

        END IF; 


    EXCEPTION
       WHEN UTL_FILE.INVALID_PATH THEN 
           DBMS_OUTPUT.PUT_LINE ('invalid_path'); 
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INVALID_MODE THEN 
           DBMS_OUTPUT.PUT_LINE ('invalid_mode'); 
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INVALID_FILEHANDLE THEN 
           DBMS_OUTPUT.PUT_LINE ('invalid_filehandle'); 
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INVALID_OPERATION THEN 
           DBMS_OUTPUT.PUT_LINE ('invalid_operation'); 
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.READ_ERROR THEN  
           DBMS_OUTPUT.PUT_LINE ('read_error');
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.WRITE_ERROR THEN 
          DBMS_OUTPUT.PUT_LINE ('write_error'); 
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
           RAISE;

       WHEN UTL_FILE.INTERNAL_ERROR THEN 
          DBMS_OUTPUT.PUT_LINE ('internal_error'); 
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          RAISE;            
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('other write error'); 
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          RAISE;
    END;