Workaround for Oracle 9i outputting CRLF when using utl_file.putraw() for BLOBs?

Question

I'm writing a database procedure to archive data in an Oracle 9i table that contains BLOBs. Because the database size has grown too large, our strategy is to export the BLOBs to the file system (Windows 2000 server), where they can be backed up on tape, then to truncate the database table.

Here's my procedure that saves a BLOB to disk:

      PROCEDURE blob_to_file
      (
          -- BLOB to be written to file
          pi_blob                     IN BLOB,

          -- Name of Oracle Directory object
          pi_oracle_directory_name    IN  VARCHAR2,

          -- Destination filename
          pi_file_name                IN  VARCHAR2
      )
      IS

        v_out_file        UTL_FILE.FILE_TYPE;
        v_blob_len        INTEGER;

        v_buffer          RAW(32767);
        v_amount          BINARY_INTEGER := 32767;
        v_pos             INTEGER := 1;

      BEGIN

      v_out_file := utl_file.fopen(
          location      => pi_oracle_directory_name,
          filename      => pi_file_name,
          open_mode     => 'W',
          max_linesize  => 32767);

      v_blob_len := dbms_lob.getlength(pi_blob);

      WHILE (v_pos &lt v_blob_len) LOOP

        -- Ensure amount read is not less than remaining BLOB amount
        IF (v_pos + v_amount) &gt (v_blob_len + 1) THEN
          v_amount := v_blob_len - v_pos;
        END IF;

        -- Read chunk of BLOB into buffer
        DBMS_LOB.read(
          lob_loc   => pi_blob,
          amount    => v_amount,
          offset    => v_pos,
          buffer    => v_buffer);

        -- Output the buffer as raw data into the file stream
        utl_file.put_raw(
          file      => v_out_file,
          buffer    => v_buffer,
          autoflush => true);

        v_pos := v_pos + v_amount;

      END LOOP;

      -- Close the file
      UTL_FILE.FCLOSE(v_out_file);

      EXCEPTION

        WHEN OTHERS THEN
          -- Close the file if something goes wrong.
          IF UTL_FILE.is_open(v_out_file) THEN
            UTL_FILE.fclose(v_out_file);
          END IF;
          RAISE;

    END blob_to_file;

And here's my procedure that iterates through the BLOBs in the table and archives them:

    PROCEDURE archive_letter_table
      (
              -- Name of Oracle Directory object
              pi_oracle_directory_name    IN  VARCHAR2
      )
      IS

        v_out_filename    NVARCHAR2(100);
        v_blob            BLOB;

        CURSOR letter_cursor IS
          SELECT
            letter_id
            ,template_ref
            ,rtf
            ,xml_data
            ,row_version
            ,file_name
            ,document_type
            ,document
            ,business_entity_id
            ,entity_type
            ,date_created
            ,sec_function_ref
            ,user_account_ref
            ,hsp
          FROM
            fusion.lms_letter;

      BEGIN

        FOR letter_cursor_row in letter_cursor LOOP

          IF letter_cursor_row.document IS NOT NULL THEN

            -- Retrieve BLOB and determine its size
            v_blob := letter_cursor_row.document;
            v_out_filename := CAST(letter_cursor_row.letter_id AS VARCHAR2) || '_' || letter_cursor_row.file_name;

            -- Call procedure to write the BLOB to file        
            FILE_UTILS.blob_to_file(v_blob, pi_oracle_directory_name, v_out_filename);

          END IF;

        END LOOP;

        TRUNCATE fusion.lms_letter;

      END archive_lms_letter_table;

My 2 problems are:

1) After running the "archive_letter_table" procedure, the files saved to disk contain CR and LF characters instead of just an LF character. I can do a global search/replace manually which fixes them, but need an automated PL/SQL solution. Apparently there is an Oracle bug #2546782 where the output from "utl_file.put_raw()" is incorrect due to inserting CR and LFs. This almost certainly seems to be causing my problem. It's mentioned down the bottom of this page: http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Utl_File/start.htm

Does anyone know of a way that I can strip out these CRLF's inside my PL/SQL procedure?

2) The files can either be PDFs or RTFs. Saving the PDFs works well (except for the CRLF problem), but the RTFs fail with the following error:

Error starting at line 5 in command:
begin
fusion.FUSION_ARCHIVE.archive_lms_letter_table('LMS_Letter_Archive_Dir');
end;
Error report:
ORA-29285: file write error
ORA-06512: at "FUSION.FILE_UTILS", line 73
ORA-06512: at "FUSION.FUSION_ARCHIVE", line 59
ORA-06512: at line 2
29285. 00000 -  "file write error"
*Cause:    Failed to write to, flush, or close a file.
*Action:   Verify that the file exists, that it is accessible, and that
           it is open in write or append mode.

The PDF files range in size between 69KB to 219KB, whereas the RTF should be 633KB. However because of the error above it ends up being 7KB on disk and obviously can't be read. By comparing the original RTF to the one that my procedure attempts to save to disk, I can see that my procedure fails to save the last line of the document (which is obviously very long... 626KB). I suspect this is something else to do with put_raw not handling lines so long.

Does anyone have any clues on how to get around this problem?

Please feel free to respond if you have a solution to just 1 of the problems. Any help greatly appreciated.

Ash

Mac Mac · Accepted Answer · 2009-07-29T09:07:27

As for your CR/LF problem, it seems like you have been Oracled (bad thing, obviously...) :

At the time of this writing, there are no workarounds nor is Oracle considering back-porting a fix for Oracle9i.

You could gain more insight by delving into these Ask Tom answers. In your case, I guess developing a Java package would be an option worth being considered.

Workaround for Oracle 9i outputting CRLF when using utl_file.putraw() for BLOBs?

3 Answers