[SOLVED] UTL_FILE using append mode creates repeating headers

UTL_FILE using append mode creates repeating headers

How to use "A"ppend mode in UTL_FILE package, but only create one header (not repeating)? Is it possible? I'm appending data, but everytime it appends, it will create repeating headers.

My code:

CREATE OR REPLACE PROCEDURE p_test AS
CURSOR c_test IS
select blah, blah from dual;

 v_file  UTL_FILE.FILE_TYPE;
 v_header   varchar2(25);

BEGIN
 v_file := UTL_FILE.FOPEN(location  => 'my_dir',
                       filename     => 'filetest09102019.csv',
                       open_mode    => 'A',
                       max_linesize => 32767);
 If file exists = 0 then        --using fgetattr; if I use 1, repeating headers will print
     v_header := 'col1, col2, col3';
     utl_file.put_line (v_file, v_header); 
 Else null; end if;     --unfortunately headers did not print at all when false/0                  
 FOR cur_rec IN c_test LOOP
UTL_FILE.PUT_LINE(v_file, data from c_test );
END LOOP;
UTL_FILE.FCLOSE(v_file);

EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
END;

Solution

If you are calling this procedure multiple times, you will get the header appended to the file each time the procedure is called.

You could first check if the file exists before appending the header, e.g. using fgetattr to detect if the file is going to be appended Check if a file exists?

Alternatively, modify your code so that it only calls the procedure once and writes all the data in one go, without appending.