0

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;
keerthana r
  • 5
  • 1
  • 1
    short answer: something changed. I'm guessing it wasn't the procedure's code, or you wouldn't be asking. That leaves the database. Perhaps a patch was applied that closed a loophole your code was depending on. I've been in this business since 1981 and believe me when I say that the landscape is littered with code that got broke by patches or upgrades - because it depended on undocumented and incorrect behavior. In the end it doesn't really matter. Your procedure _should_ close the file, so just fix it. – EdStevens Oct 24 '20 at 21:26

1 Answers1

1

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.

Andrew Sayer
  • 2,296
  • 1
  • 7
  • 9