CODEWITHSUNDEEP
oracle-databaseplsqlutl-file
BFF
BFF

Reputation: 396

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;

Upvotes: 1

Views: 1941

Answers (2)

APC
APC

Reputation: 146329

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:

  1. Test whether the file exists (like this one)
  2. If the file doesn't exist, create the file in Write mode, write the header and then close the file
  3. 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.

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

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.

Upvotes: 2

Related Questions