Extract BLOB data from Oracle Database and convert to original file

Question

I am trying to extract BLOB data and convert it to the original file (pdf, rtf, doc, etc). Below is my code:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       NUMBER := 1;
  l_blob      BLOB;
  l_blob_len  NUMBER;
BEGIN

  SELECT file_contents
    INTO   l_blob
    FROM   irb_files
   WHERE  FILE_NAME = 'STD_FlipBook_Religion2013.doc';

  l_blob_len := DBMS_LOB.getlength(l_blob);

  -- Open the destination file.
  l_file := UTL_FILE.fopen('IRB','STD_FlipBook_Religion2013.doc','wb','W');


  DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
  UTL_FILE.put_raw(l_file, l_buffer, TRUE);
  l_pos := l_pos + l_amount;

  -- Close the file.
  UTL_FILE.fclose(l_file);

END;

I am relatively new to all of this so I am sorry if my code is a bit off. The error I keep getting is:

Error report:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 18
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Any help would be greatly appreciated.

Database Info: Oracle9i Enterprise Edition Release 9.2.0.5.0

Answer 1

The problem is with this line:

l_file := UTL_FILE.fopen('IRB','STD_FlipBook_Religion2013.doc','wb','W');

The docs show the signature of the function below. The last parameter you have, 'W', corresponds to the 'max_linesize', which is expected to be a number. So, you cannot convert 'W' to a number. I think you can simply use the default value in your case, so just remove the 'W' parameter.

(from Oracle 9.2 docs)

FOPEN Function This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously. See also "FOPEN_NCHAR Function".

Syntax UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;

Parameters Table 95-3 FOPEN Function Parameters Parameter Description location Directory location of file.

filename File name, including extension (file type), without directory path. In Unix, the filename cannot end with /.

open_mode Specifies how the file is opened. Modes include:

r--read text

w--write text

a--append text

If you try to open a file that does not exist using a value for open_mode, then the file is created in write mode.

max_linesize Maximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767). The default is approximately 1000 bytes.