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;
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:
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.