How to extract / download / unpack a BLOB with large data into a CSV File using raw column

How to extract / download / unpack a BLOB with large data into a CSV File using raw column

This example will help to upload larger taxt into blobs and later download / extract into a csv file. Normally this can be achived using CLOBS but what if your CSV file goes upto 4 GB? I think this is the best way in a  situation like that.

Following procedure requires two parameters
 1. A reference key to read the blob from database table (The blob must already be stored in a
     database table).
2.  Directory where you want to create a CSV file.

The output will return 'OK' if the procedure ran successfully.

CREATE OR REPLACE PROCEDURE
                         CREATE_CSV_USING_BLOB(P_BLOB_ID in number,
                                                                             P_DIRECTORY IN VARCHAR2,
                                                                             P_RESULT OUT VARCHAR2) is
    V_BLOB BLOB;
    V_START NUMBER := 1;
    V_BLOB_CHUNK_LENGTH NUMBER;
    V_READ_BLOB_IN_RAW RAW(32000);
    V_BLOB_LENGTH NUMBER;
    V_FILENAME VARCHAR2(200);
    V_OUTPUT_FILENAME utl_file.file_type;
  BEGIN
    P_RESULT := 'OK';
    V_START := 1;
    V_BLOB_CHUNK_LENGTH := 32000;
    -- get length of blob
    SELECT dbms_lob.getlength(FILE_BLOB)
    INTO V_BLOB_LENGTH
    FROM UPLOAD_STREET_FILES
    WHERE  FILE_ID =  P_BLOB_ID;
   -- select blob into variable
    SELECT FILE_BLOB,
           FILE_NAME
    INTO   VBLOB,
           V_FILENAME
    FROM   UPLOAD_STREET_FILES
    WHERE  FILE_ID =  P_BLOB_ID;
   -- define output file which will store the csv data

    V_OUTPUT_FILENAME := utl_file.fopen(P_DIRECTORY , V_FILENAME,'wb', 32760);
    -- if small enough for a single write just do it
    IF V_BLOB_LENGTH < 32760 THEN
      utl_file.put_raw(V_OUTPUT_FILENAME,V_BLOB);
      utl_file.fflush(V_OUTPUT_FILENAME);
    ELSE -- write in small pieces and carry on until finished
      V_START := 1;
      WHILE V_START < V_BLOB_LENGTH and V_BLOB_CHUNK_LENGTH > 0
      LOOP
        dbms_lob.read(V_BLOB,V_BLOB_CHUNK_LENGTH,V_START,V_READ_BLOB_IN_RAW);
        utl_file.put_raw(V_OUTPUT_FILENAME,V_READ_BLOB_IN_RAW);
        utl_file.fflush(V_OUTPUT_FILENAME);
        -- set the start position for the next chunk to read
        V_START := V_START + V_BLOB_CHUNK_LENGTH;
        -- set the end position if less than 32000 bytes (could be reading the last line)
        V_BLOB_LENGTH := V_BLOB_LENGTH - V_BLOB_CHUNK_LENGTH;
        IF V_BLOB_LENGTH < 32000 THEN
           V_BLOB_CHUNK_LENGTH := V_BLOB_LENGTH;
        END IF;
     END LOOP;
    END IF;
    utl_file.fclose(V_OUTPUT_FILENAME);
    dbms_output.put_line( P_RESULT );
    EXCEPTION
     wheN OTHERS THEN
       dbms_output.put_line (SQLERRM );
       P_RESULT := SQLERRM ;
  END;
 /

SQL> EXECUTE CREATE_CSV_USING_BLOB(1001, 'UNIX_CSV_OUTPUT_DIRECTORY' );