utl file oracle with buffer

Question

I've read this article about Tuning UTL_FILE

Technically the approach is to concatenate records while the size is less than maximum length of the buffer and write the entire buffer when the length is greater

Excerpt from site (code):

         IF LENGTH(v_buffer) + c_eollen + LENGTH(r.csv) <= c_maxline THEN
            v_buffer := v_buffer || c_eol || r.csv;
         ELSE
            IF v_buffer IS NOT NULL THEN
               UTL_FILE.PUT_LINE(v_file, v_buffer);
            END IF;
         v_buffer := r.csv;
         END IF;

So, I've decided to to move this one in a function

-- constants 
  C_CHR                                  CONSTANT VARCHAR2(2)  := CHR(10);
  C_CHRLEN                               CONSTANT PLS_INTEGER  := LENGTH(C_CHR);
  C_MAXLEN                               CONSTANT PLS_INTEGER  := 32767;

function FN_GET_BUFFER(p_rec IN VARCHAR2, p_buffer IN VARCHAR2) RETURN VARCHAR2
  is
  begin
      IF LENGTH(p_buffer) + C_CHRLEN + LENGTH(p_rec) <= C_MAXLEN THEN
        RETURN p_buffer || C_CHR || p_rec;
      ELSE
        IF p_buffer IS NOT NULL THEN
            RETURN p_buffer;
        END IF;
        RETURN p_rec;
      END IF;
  end FN_GET_BUFFER;

And here's how I call my function which doesn't work as expected..

procedure export as 
    l_tmp_file_name VARCHAR2(30);
    l_csv_file_name VARCHAR2(30);
    l_file          UTL_FILE.FILE_TYPE;
    l_buffer        VARCHAR2(32767);

    CURSOR cur_table
    IS
    SELECT * FROM table    
begin
    l_tmp_file_name := 'file.tmp';
    BEGIN
        l_file := UTL_FILE.FOPEN(C_DIRECTORY_PATH, l_tmp_file_name,'A',C_MAXLEN);          
        FOR rec IN cur_table
        LOOP
            l_rec := CONVERT(rec.id || ',' || rec.user ,'AL32UTF8');            
            l_buffer := l_buffer || FN_GET_BUFFER(l_rec, l_buffer);
            if l_buffer is NOT NULL then
                UTL_FILE.PUT_LINE(l_file, l_buffer);
                l_buffer := NULL;
            end if;
       END LOOP rec;    
       UTL_FILE.FCLOSE(l_file);   
       l_csv_file_name := 'file.csv';
       UTL_FILE.FRENAME(src_location => C_DIRECTORY_PATH, src_filename => l_tmp_file_name, dest_location => C_DIRECTORY_PATH, dest_filename => l_csv_file_name, overwrite => FALSE);
   EXCEPTION
      WHEN OTHERS THEN
          UTL_FILE.FCLOSE(l_file);
          UTL_FILE.FREMOVE(location => C_DIRECTORY_PATH, filename => l_tmp_file_name);
    END;
end export;

The problem is that I get

1,user1
2,user2
3,user3
4,user4
5,
user5
6,user6
7,user7
8,user8
9,user9
10,user10
11,user11
12,user12
13,user13
14,
user14
15,user15
16,user16
17,user17
18,user19

As you can see, after 4 records the buffer is 'full' so it writes instead the buffer which is user14 instead of writing all on the same line

Thank you

Answer 1

The problem is not your function as such, it's the test you make after the call:

if l_buffer is NOT NULL then
            UTL_FILE.PUT_LINE(l_file, l_buffer);
            l_buffer := NULL;
end if;

l_buffer is always populated, it's never null. So the test is always true and you write to the file for each row in the table. You need to test for the length of l_buffer and only write when the length is greater than your limit.

But don't just change the test. You need to unpick the logic of FN_GET_BUFFER() to include the buffer population and flushing in a single subroutine, otherwise you will lose data. Something like this:

FOR rec IN cur_table
LOOP
    l_rec := CONVERT(rec.id || ',' || rec.user ,'AL32UTF8');            

    IF LENGTH(l_buffer) + LENGTH(C_CHRLEN) + LENGTH(l_rec) > C_MAXLEN THEN
        -- buffer full, write to file 
        UTL_FILE.PUT_LINE(l_file, l_buffer);
        l_buffer := l_rec;
    ELSIF LENGTH(l_buffer) = 0 THEN 
        -- first record
        l_buffer := l_rec;
    ELSE
       -- buffer not full
       l_buffer := _l_buffer || C_CHRLEN || l_rec;
    END IF;
END LOOP rec;
if LENGTH(l_buffer) > 0 THEN
   -- end of table, write last record
   UTL_FILE.PUT_LINE(l_file, l_buffer);
end if;

warning coded wildstyle, not tested