oracleutl-file

Reason for data inconsistency if UTL_FILE is not closed after writing data into the file in Oracle SQL


I am using Oracle SQL developer as a client for Oracle 11g DB. Its a simple issue. I am fetching data from a table and writing the data into a text file. This particular piece of code is scheduled as a monthly job and the output text file is placed in DB directory path.

The number of records differ each month. The text output file had correct number of rows as like in table till last month's job. This month, data inconsistency is observed in the text file. The number of rows to be exported to text file is lets say, 1000. The output file has total of 950 or so rows . The data do not match. This issue was not occurring till last month. On testing further, observed, file was not closed after writing using UTL_FILE.FCLOSE(M_OUT_SYS). Issue is resolved after closing the file, data matches now.

But why the issue didn't surface till last month when program ran without file closure and why the issue surfaced suddenly in this month?

    declare
    M_OUT_SYS UTL_FILE.FILE_TYPE;
      M_DATA    VARCHAR2(2000);
      M_DIRECTORY_NAME      ALL_DIRECTORIES.DIRECTORY_NAME%TYPE;
      M_DELIMITER_FILE_NAME VARCHAR2(250);
    cursor c1 is
    select * from example_table;
    begin
    M_DIRECTORY_NAME := 'OracleDB_dir_name';
    M_DELIMITER_FILE_NAME := 'OutputTextFile.txt';
    M_OUT_SYS := UTL_FILE.FOPEN(M_DIRECTORY_NAME,
                                    M_DELIMITER_FILE_NAME,
                                    'W', 8192);
        UTL_FILE.PUT_LINE(M_OUT_SYS,'column1|column2|column3');
    for i in c1 loop
    M_DATA := I.column1 || '|' || I.column2 || '|' || I.column3;
    UTL_FILE.PUT_LINE(M_OUT_SYS, M_DATA);
    end loop;
    end;

Solution

  • See the utl_file docs for 11.2 https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm : UTL_FILE.PUT_LINE does not (by default) flush to the file after each call, it just writes to a buffer. Flushing will happen after either:

    1. The instance decides to flush due to reaching a certain buffer size (around 10KB)
    2. Data is manually flushed with utl_file.fflush
    3. The file handle is closed
    4. The session disconnects (which is similar to 3)

    My money would be on your previous jobs exited their session by the time you came to pick up the file. And when you noticed the difference it's because the session was still open and it had last triggered an auto flush on the 950th row.