utl_file: to fflush or not to fflush?

Hi,

I am having a hard time with the code below (8.1.5, solaris). If the blob being put is > 32768, then the produced filesize is always 32768. The fflush in the loop is needed to write out the complete file. I think that's not what's documented.

Any comments ?

Thanks, Joost

--


CREATE OR REPLACE PROCEDURE unload
 (v_albumno IN albums.albumno%TYPE,
  v_trackno IN tracks.trackno%TYPE)
IS
 chunksiz       INTEGER := 32765;
 data_buffer    RAW(32767);
 temp_buffer    VARCHAR2(32767);
 amount         INTEGER;
 lobsiz         INTEGER;
 position       INTEGER;
 temp_blob      BLOB;
 trackname      STRING(1024);
 file_p         BFILE;
 filehandle     utl_file.file_type;
BEGIN

  /*
   * figure out the track's filename
   */
   trackname := trackfilename(v_albumno, v_trackno);

  /*
   * select the lob into a temporary lob
   */
  SELECT mp3 INTO temp_blob
    FROM tracks t, albums a
   WHERE t.albumno = a.albumno
     AND t.albumno = v_albumno
     AND t.trackno = v_trackno;



   /*

    * get length of internal lob and open the dest. file.
    */
   lobsiz := dbms_lob.getlength(temp_blob);



   /*

    * Bail out if the LOB is empty. Nothing to do then
    */
   IF (lobsiz = 0) THEN
    dbms_output.put_line('Track '||v_albumno||'.'||v_trackno||' is empty');
    return;
   END IF;

   filehandle := utl_file.fopen('/usr/local/tmp', trackname, 'w', chunksiz);



   /*

    * write out lob data in chunks.
    */
   position := 1;
   dbms_output.put_line ('Unloading: ' ||trackname|| '.');

   WHILE (position < lobsiz) LOOP
    IF ( (lobsiz - position) > chunksiz) THEN
     amount := chunksiz;
    ELSE
     amount := lobsiz - position + 1;
    END IF;

    dbms_lob.read (temp_blob, amount, position, data_buffer);
    position := position + amount;
    temp_buffer := utl_raw.cast_to_varchar2(data_buffer);
    utl_file.put (filehandle, temp_buffer);
    /*
     * evil fflush: if it is not here, every file is 32768 bytes after the
     * the file is closed. Looks like put hase some arithmic problems.
     */
    utl_file.fflush(filehandle);
   END LOOP;



   /*

    * close the file
    */
   utl_file.fclose(filehandle);
END;
/