how to save blob images from oracle table to jpeg into local pc

by sathish


i want to download all the images stored into oracle table as BLOBs.
These are to be uploaded into SAP system.

But i don't know the query/procedure to extract image from oracle to local pc in jpeg fromat.

Please help me in this .
how to solve my problem.
Please give the query to download images.

Whilst Oracle provides a PL/SQL procedure LOADBLOBFROMFILE in the DBMS_LOB package which allows you to upload a binary file to the database it doesn't unfortunately provide a procedure to do the opposite, so you have to roll your own code.

Before we get too involved in writing code, we should check to see if there is an easier way of doing things. SAP is an application that sits on top of a database, so if that database is an Oracle database we can use DataPump or a database link to transfer the LOBs from the "old" database to the "new" one.

Oracle allows the use of UPDATE on a table to set the value of the LOB to that of a LOB in a remote database as in the following example.

UPDATE my_tab SET my_lob = (SELECT my_lob FROM source_table@source_database);

Although SAP is a closed source application it may be possible to find out which tables need to be populated. If you can't do that then you have to write your own code to download the images and use the Oracle PL/SQL built-in packages DBMS_LOB and UTL_FILE.

Let's have a look at a bare-bones solution. We start by opening and reading the LOB and then write it to a file on the database server. We have a small problem though in that we can only read 32767 bytes at a time from the BLOB so obviously we need to read it in a loop.


 src BLOB;
 buf RAW(32767);


 FOR src_blob IN (SELECT blob_loc,blob_name FROM src_table) LOOP

    /* new image */
   fil := UTL_FILE.FOPEN (
location => 'blob_dir',
filename => src_blob.blob_name,
open_mode => 'w');

   pos := 1;
   len := DBMS_LOB.GETLENGTH (lob_loc => src_blob.blob_loc);

   WHILE pos <= len LOOP

lob_loc => src_blob.blob_loc,
amount => 32767, -- max size of RAW buffer
offset => pos,
buffer => buf);

     pos := pos + 32767;

     UTL_FILE.PUT_RAW(fie => fil, buffer => buf);
-- write buffer to file


   UTL_FILE.FFLUSH(fil); -- flush buffer to file



The general algorithm then is for each blob, open a new file, write the blob to the file in chunks, close the file. A straightforward algorithm, as always the implementation is not quite so straightforward.

N.B. the above pl/sql code has not been tested at all, has no error handling and comes without any guarantees, not least that it is error free! Also UTL_FILE uses an Oracle directory which must be defined and write access must have been granted to the user running this code.

For details on utl_file and dbms_lob see the Oracle Database PL/SQL Packages and Types Reference manual

You would probably also find this question and answer on using utl_file to be helpful.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.