data loss with parallel enabled pipelined function

Question

I have a pipelined function that loads data into file.

The following is the code of function.

CREATE OR REPLACE FUNCTION DATA_UNLOAD
   ( p_source                 IN SYS_REFCURSOR,
      p_filename       IN VARCHAR2,
        p_directory      IN VARCHAR2
       ) RETURN dump_ntt PIPELINED PARALLEL_ENABLE (PARTITION p_source BY ANY)
AS
   TYPE row_ntt IS TABLE OF VARCHAR2(32767);
   v_rows       row_ntt;
   v_file       UTL_FILE.FILE_TYPE;
   v_buffer     VARCHAR2(32767);
   v_sid        VARCHAR(255);
   v_name       VARCHAR2(255);
   v_lines      PLS_INTEGER := 0;
   v_start_dttm TIMESTAMP WITH TIME ZONE:= SYSTIMESTAMP;
   v_end_dttm   TIMESTAMP WITH TIME ZONE;
   c_eol        CONSTANT VARCHAR2(1) := CHR(10);
   c_eollen     CONSTANT PLS_INTEGER := LENGTH(c_eol);
   c_maxline    CONSTANT PLS_INTEGER := 32767;   
BEGIN
  --v_sid := lpad(sys_context('USERENV', 'sid'), 10, '0');
  v_name:=p_filename;
  LOOP 
     if utl_file.is_open(v_file)
     then
        utl_file.fclose(v_file); 
     end if;
    v_file := UTL_FILE.FOPEN(p_directory, v_name, 'A', c_maxline);
    FETCH p_source BULK COLLECT INTO v_rows LIMIT 100;
  FOR i IN 1 .. v_rows.COUNT LOOP
     IF LENGTH(v_buffer) + c_eollen + LENGTH(v_rows(i)) <= c_maxline THEN
        v_buffer := v_buffer || c_eol || v_rows(i);
     ELSE
        IF v_buffer IS NOT NULL THEN
           UTL_FILE.PUT_LINE(v_file, v_buffer);
         END IF;
        v_buffer := v_rows(i);
      END IF;
    END LOOP;
    v_lines := v_lines + v_rows.COUNT;
    EXIT WHEN p_source%NOTFOUND;
  END LOOP;
  CLOSE p_source;   
   UTL_FILE.PUT_LINE(v_file, v_buffer);
   UTL_FILE.FCLOSE(v_file);
   v_end_dttm := SYSTIMESTAMP;
   --PIPE ROW (dump_ot(v_name, p_directory, v_lines, v_sid, v_start_dttm, v_end_dttm));
   --RETURN ;
END;

i call the function this way.

SELECT * from table(DATA_UNLOAD(
                         CURSOR(select /*+ PARALLEL */ a || b || c from sample_table),                                     
                        'sample.txt',
                         '99_DIR'));

a real life select that i pass as a parameter to function returns 30000 rows, but when i use the function to load the result into a file some rows are lost. During the execution with PARALLEL hint there are 24 parallel sessions, and i dont want to make it less. My guess is that the problem is in parallel execution, because when i dont use PARALLEL hint no data is lost. Can anyone suggest something to get rid of that problem without removing the hint?

Answer 1

Even though you are creating sample.txt with Append mode - you have 24 parallel sessions each writing to it. I always use unique filenames by appending the SID to your variable:

SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1;
v_name := p_filename || '_' || v_sid || '.dat';

Depending on the # of parallel sessions you should 1 to many files with the format sample_nnnn.txt where nnnn is the SID number.