How to download blob file from oracle table.

Unlike normal data , blob data needs Pl/sql blocks to download in a particular directory. Sometimes we need to download blob(pdf,image etc) from tables. PFB Steps to download BLOB files :-

1. Insert Blob data to a test table. PFB steps to insert Blob data.

2. Create  directory named DATAPUMP where file will be downloaded.

SQL> create directory datapump as '/amit/datapump;

Directory created

SQL> grant read,write on directory datapump to amit;

Grant succeeded.

3. Now as per above link Blob data has been inserted in LOB_INSERT table. PFB structure and data present in LOB_INSERT table:-

SQL> desc lob_insert
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOB_ID                                                  NUMBER(12)
 LOB_NAME                                           VARCHAR2(80)
 FILE_SIZE                                            NUMBER
 LOB_FILE                                             BLOB

SQL> select * from lob_insert;

    LOB_ID LOB_NAME                        FILE_SIZE LOB_FILE
---------- ------------------------------ ---------- ----------------------------------------
         1 filenumber 1                                               3C68746D6C3E0A3C686561643E0A3C6D65746120
                                                                               687474702D65717569763D22436F6E74656E742D
                                                                               547970652220636F6E74656E743D22746578742F
                                                                               68746D6C3B20636861727365743D55532D415343

         2 filenumber 2                                               3C68746D6C3E0A3C686561643E0A3C6D65746120
                                                                              687474702D65717569763D22436F6E74656E742D
                                                                             547970652220636F6E74656E743D22746578742F
                                                                             68746D6C3B20636861727365743D55532D415343

         3 filenumber 3                                              3C68746D6C3E0A3C686561643E0A3C6D65746120

    LOB_ID LOB_NAME                        FILE_SIZE LOB_FILE
---------- ------------------------------ ---------- ----------------------------------------
                                                                              687474702D65717569763D22436F6E74656E742D
                                                                             547970652220636F6E74656E743D22746578742F
                                                                             68746D6C3B20636861727365743D55532D415343

LOB_FILE column has blob data.

4. Now to download Blob files to DATAPUMP directory run below PL/SQL procedure :-

DECLARE

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blobname varchar2(50);

l_blob_len INTEGER;

ctrl integer;

BEGIN

-- Get LOB locator

select max(LOB_ID) into ctrl from LOB_INSERT;   -- used to download all files of table

for i in 1 .. ctrl loop

SELECT LOB_file,LOB_name

INTO l_blob,l_blobname

FROM LOB_INSERT

where lob_id=i;

l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the file.in Datapump Directory already created.

l_file := UTL_FILE.fopen('DATAPUMP',l_blobname,'WB', 32767);  

-- File will be created with the name stored as in LOB_NAME column

-- Read chunks of the BLOB and write them to the file created in directory until complete.

WHILE l_pos < l_blob_len LOOP

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;

END LOOP;    -- This will end the While loop when condition met.

-- To read next blob file these variables need to be reset again

l_blob_len :=0;               

l_pos :=1;

-- Close the file.

UTL_FILE.fclose(l_file);

end loop;       -- This will end the FOR loop when all BLOB files in the table have been downloaded.

EXCEPTION

WHEN OTHERS THEN

-- Close the file if something goes wrong.

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

/

5. Check Directory DATAPUMP that File has been created with name as it has in LOB_NAME column.

bash-3.2$ cd /amit/datapump/

bash-3.2$ ls -ltr|tail

-rw-r--r--    1 oracle   oinstall    3280618 Sep 24 00:40 filenumber 2

-rw-r--r--    1 oracle   oinstall    3280618 Sep 24 00:40 filenumber 1

-rw-r--r--    1 oracle   oinstall    3280618 Sep 24 00:40 filenumber 3

All blob files have been Downloaded successfully. 

I hope this article helped you.

Regards,

Amit Rath