Oracle utl_file throwing error when the input buffer size exceeds more than 1000 character

Question

I'm tyring to write files for every cursor execution as shown in below sql. The data_payload column will have more than 1000 characters.

There is an exception at utl_file.put line. If I use SUBRTR of 1000 characters, then the file writes successfully. Is there any other alternate function to handle this?

   SET SERVEROUTPUT ON
DECLARE
    l_file_name   VARCHAR2(100);
    l_chr_payload VARCHAR2(4000);
    fhandle       utl_file.file_type;
    CURSOR payload_cur IS
    SELECT data_payload,
           request_reference
      FROM temp_tbl
     WHERE message_type = 'ORDERCREATE'
       AND ROWNUM < 2;
    TYPE payload_typ IS
        TABLE OF payload_cur%rowtype INDEX BY BINARY_INTEGER;
    l_col_payload payload_typ;
BEGIN
    OPEN payload_cur;
    l_col_payload.DELETE;
    FETCH payload_cur
    BULK COLLECT INTO l_col_payload;
    CLOSE payload_cur;
    FOR i IN 1..l_col_payload.count LOOP
        l_file_name   := l_col_payload(i).request_reference
                       || '_'
                       || i
                       || '.json';
        dbms_output.put_line('l_file_name' || l_file_name);
        fhandle       := utl_file.fopen(
                                 'TMP_DIR'     -- File location
                                 ,
                                 l_file_name -- File name
                                 ,
                                 'w' -- Open mode: w = write. 
                   );
        l_chr_payload := substr(
                               l_col_payload(i).data_payload,
                               1,
                               1000
                         );
        utl_file.put(
                    fhandle,
                    l_chr_payload
        );
        utl_file.fclose(fhandle);
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line('ERROR: '
                             || sqlcode
                             || ' - '
                             || sqlerrm);
        RAISE;
END;
/

Answer 1

Assuming that temp_tbl.data_payload is a CLOB data type then you want to loop through the payload reading substring chunks (without needing to store the substrings in a temporary variable).

If you want to write more than 1024 characters at one time then you need to specify the 4th argument to UTL_FILE.FOPEN(location, filename, openmode, max_linesize) as the default is 1024.

Something like this (untested as I don't have your tables or a directory to write to):

DECLARE
  l_index       PLS_INTEGER := 0;

  CURSOR payload_cur IS
    SELECT data_payload,
           request_reference
      FROM temp_tbl
     WHERE message_type = 'ORDERCREATE'
       AND ROWNUM < 2;

  TYPE payload_typ IS TABLE OF payload_cur%rowtype;
  l_col_payload payload_typ;
BEGIN
  OPEN payload_cur;

  LOOP
    -- In general, you should limit the number of rows you process at one time.
    -- In your case this is not necessary due to the `ROWNUM < 2` filter.
    FETCH payload_cur BULK COLLECT INTO l_col_payload LIMIT 10;
    EXIT WHEN payload_cur%NOTFOUND;

    FOR i IN 1..l_col_payload.count LOOP
        l_index := l_index + 1;
        DECLARE
          l_file_name VARCHAR2(100);
          c_amt       CONSTANT PLS_INTEGER := 32000;
          l_length    PLS_INTEGER := COALESCE(dbms_lob.getlength(l_col_payload(i).data_payload),0);
          l_offset    PLS_INTEGER := 1;
          l_fhandle   utl_file.file_type;
        BEGIN
          l_file_name := l_col_payload(i).request_reference || '_' || l_index || '.json';
          dbms_output.put_line('l_file_name' || l_file_name);
          l_fhandle   := utl_file.fopen('TMP_DIR', l_file_name, 'w', 32760);
          WHILE ( l_offset <= l_length ) LOOP
            utl_file.put(
              l_fhandle,
              dbms_lob.substr(l_col_payload(i).data_payload,c_amt,l_offset)
            );
            utl_file.fflush(l_fhandle);
            l_offset := l_offset + c_amt;
          END LOOP;
          utl_file.fclose(l_fhandle);
        END;
    END LOOP;
  END LOOP;

  CLOSE payload_cur;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('ERROR: ' || sqlcode || ' - ' || sqlerrm);
    RAISE;
END;
/