Source code

EXTRACT_LOB_TO_FILE

PROCEDURE EXTRACT_LOB_TO_FILE (
  p_fileID NUMBER,
  p_directory VARCHAR2) IS
    var_fileName   VARCHAR2 (100);
    blob_data      BLOB;
    num_startPos   NUMBER := 1;
    num_byteLen    NUMBER := 32000;
    len            NUMBER;
    my_vr          RAW (32000);
    x              NUMBER;
    l_output       UTL_FILE.FILE_TYPE;
 
BEGIN
    SELECT tab4.FILE_NAME, tab4.FILE_DATA
      INTO var_fileName, blob_data
      FROM FND_LOBS tab4
     WHERE file_id = p_fileID;

    -- define output directory
    l_output := UTL_FILE.FOPEN (p_directory, var_fileName, 'wb', 32760);

    num_startPos := 1;
    num_byteLen := 32000;

    -- get length of blob
    SELECT DBMS_LOB.GETLENGTH (blob_data) INTO len FROM DUAL;

    -- save blob length
    x           := len;

    -- if small enough for a single write
    IF len < 32760 THEN
      UTL_FILE.put_raw (l_output, blob_data);
      UTL_FILE.fflush (l_output);
    ELSE           -- write in pieces
      num_startPos := 1;

      WHILE num_startPos < len AND num_byteLen > 0 LOOP
        DBMS_LOB.read (blob_data, num_byteLen, num_startPos, my_vr);
        UTL_FILE.put_raw (l_output, my_vr);
        UTL_FILE.fflush (l_output);
        -- set the start position for the next cut
        num_startPos := num_startPos + num_byteLen;
        -- set the end position if less than 32000 bytes
        x := x - num_byteLen;

        IF x < 32000 THEN
          num_byteLen := x;
        END IF;
      END LOOP;
    END IF;

    UTL_FILE.FCLOSE (l_output);
  END EXTRACT_LOB_TO_FILE;
/