About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, September 23, 2013

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

5 comments:

  1. Hi Amit,

    Thanks for the procedure. but we are getting an error as
    "
    END IF;
    RAISE;
    END;
    Error at line 1
    ORA-01426: numeric overflow
    ORA-06512: at line 50"

    The size of the file is only 12KB and the file is .zip format

    ReplyDelete
  2. Hi!
    Great publication! This very helped me.
    I've only changed the first loop like this:
    for cur in (SELECT lob_id from lob_insert)
    loop
    i:= cur.lob_id;
    ...

    ReplyDelete
  3. Thanks Amit... it helped me download the images..

    ReplyDelete
  4. This helped me A LOT! Thank you so much!

    ReplyDelete
  5. i got an error, Could you please help on this

    ORA-06550: line 14, column 12:
    PL/SQL: ORA-00932: inconsistent datatypes: expected - got BLOB
    ORA-06550: line 14, column 1:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    ReplyDelete