UTL_FILE using append mode creates repeating headers

Question

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;

score 2 · Accepted Answer · answered Sep 10 '19 at 08:09

2

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.

answered Sep 10 '19 at 08:09

Jeffrey Kemp

59,135
14
106
158

Thanks, Jeffrey. Unfortunately, the "file exists" did not work for me. The file is being generated once a day with a timestamp - it probably would work if the file already existed, and not the first run of the file generation itself (sorry, I did not mention it on the original post). Unless my logic is wrong (see above for revised code). And I can't use the "write" option, because the software is calling it and needs the append option as it won't work otherwise :( – BFF Sep 10 '19 at 13:02
That doesn't make sense - are you checking if the file exists immediately *after* you created it with fopen? You should check if the file exists *before* you open it for appending. If your file is getting multiple headers, somehow you must be calling this procedure multiple times for the same filename. I can't see any other way this might happen, unless there is other code that you haven't shown us. – Jeffrey Kemp Sep 11 '19 at 00:53
My God, you are exactly right !! Thanks, it works now! – BFF Sep 11 '19 at 12:51

APC · Answer 2 · 2019-09-10T14:11:37.680

You can solve this with a bit of design. At the moment you have one procedure which opens the file in append mode, writes the header to it, then writes the data to it. What you need is a sub-routine for opening the file. This procedure would be and implements the following logic:

Test whether the file exists (like this one)
If the file doesn't exist, create the file in Write mode, write the header and then close the file
Open the file in Append mode.

Your existing procedure now just calls the routine described above and writes the data to the opened file. Something this (using borrowed and untested 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)  := 'col1, col2, col3';

 function open_file (p_filename in varchar2
                     , p_dirname in varchar2
                     , p_header in varchar2
                      )
   return UTL_FILE.FILE_TYPE
 is
    fh UTL_FILE.FILE_TYPE;
    l_fexists boolean;
    l_flen   number;
    l_bsize  number;
    l_res    number(1);   
  begin
    utl_file.fgetattr(upper(p_DirName), p_FileName, l_fexists, l_flen, l_bsize);
    if not l_fexists  then
      fh := UTL_FILE.FOPEN(location    => p_DirName,
                           filename     => p_FileName,
                           open_mode    => 'W',
                           max_linesize => 32767);     
      utl_file.put_line (fh, p_header); 
      utl_file.fclose(fh);
    end if;
    fh := UTL_FILE.FOPEN(location    => p_DirName,
                         filename     => p_FileName,
                         open_mode    => 'A',
                         max_linesize => 32767);     
    return fh;
  end open_file;

BEGIN
 v_file := open_file 'my_dir', 'filetest09102019.csv', v_header);                  

  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;

Strictly speaking open_file () doesn't need to a private procedure in this example. But general I think it's good practice to hide low level stuff in separate procedures, because it makes the main body of the code easier to read. Also it is frequently the case that we'll want to do this in more than one place (for more than one type of file) so it's handy to encapsulate.

Thanks, haven't tried it but this also makes sense :D – BFF Sep 11 '19 at 12:52 — BFF, Sep 11 '19 at 12:52

UTL_FILE using append mode creates repeating headers

2 Answers2