Binary Large Objects – otherwise known as BLOBs. You’ve also got large objects which are composed of text – that would be a CLOB. If you hear people talk about LOB storage, they are referring to how the data in these fields are stored – outside the space reserved for the regular segments (table data.)

BLOBs are frequently used to store data such as pictures in the database. You can pretty much store anything in a BLOB, but it seems I most frequently get asked how to ‘look at pictures in the database.’ So here’s how to load data to a BLOB and how to preview the data stored in a BLOB using SQL Developer.

How to load a file to a BLOB

  • Find your table
  • The BLOB cell will say (BLOB)
  • Double-right-mouse-click
  • Hit the ‘pencil’ button
This opens the advanced edtior
  • On the ‘Local Data’ panel, use the ‘Load’ hyperlinked text
  • Navigate to your file, select and click ‘Open’
  • COMMIT your transaction
COMMITment issues? Try the button.

How to view a picture stored as a BLOB

Same process as above, at least for the first four steps. After you have opened the blob editor, you should see a ‘View as Image’ check box on the top part of the form. Click on that and SQL Developer will display the BLOB data in picture form.

A picture of The Blob stored as a BLOB

Have something other than a picture stored in that BLOB? That’s OK, use the ‘Download’ method instead. Save it to a file, then use the Log panel to quickly open your file.

The file logging feature is new for SQL Developer v3.1.

thatjeffsmith
Author

I'm a Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

66 Comments

  1. While loading BLOB to one of the column in SQL DEV 17.4 and at the time of saving I get below error. This works fine in SQL Dev 4.1 though – is this due to JDBC version or something else please?

    UPDATE “ECHNWLT”.”IMS_IMAGEDETAILS” SET WHERE ROWID = ‘AAAhYbAARAAAkVJAAA’ AND ORA_ROWSCN = ‘179639352771’

    One error saving changes to table “ECHNWLT”.”IMS_IMAGEDETAILS”:

  2. Luis Carlos Ramirez Reply

    Hi Jeff
    I’m getting the next error on the previe imagen box
    “New BMP version not implemented yet”

    I’m using Oracle 11g Xe and Oracle Linux on a Virtualbox VM… I just update the SQL developer to 170.2.0 version…

    I can view the Blob image in my Windows computer connected to Oracle 10g and using the same version of SqlDeveloper.

    • Luis Carlos Ramirez

      thank for your answer, I doesn’t work with any format… should I try update some specific java component?… or any possible idea for a solution?

  3. Akhtar Mohaimin Reply

    Hi jeff, How can i easily insert/load multiple jpg(say 10000 jpg files) into my oracle database table ? Thanks in advance.

  4. Hi Jeff,
    Could you please share your thoughts on ,how to verify the BLOB data from source ( Oracle DB) is loaded correctly into Data Lake – Hive table. Is there any method like we can get checksum in source and target and compare the BLOB data loaded is intact. Please clarify.
    Thanks & regards,
    Suma

    • thatjeffsmith

      Sorry, no idea. But I would see if you can retrieve it once loaded. BLOBs are just files. I’ve rarely (never) seen issues where the the data isn’t loaded correctly.

    • Thanks Jeff. We might need to build some interface at Hadoop side to see the file content as we have multiple file types.
      Regards,
      Suma

  5. Hi Jeff,

    We have Oracle 12c database on Linux. I have stored images(.jpg) in a BLOB using SQL Developer installed on Windows 7 machine. I have downloaded the image file on Windows7 machine from SQL Developer. On opening the image file, getting error ‘ Windows Photo viewer can’t open this picture because either Photo Viewer does not support this file format, or you dont have the latest updates to Photo viewer’.

    Please can you help.

    • thatjeffsmith

      what happens if you try to view the image from the database using the sqldev blob image viewer?

  6. Hi Jeff,

    I am using SQL Developer to transfer data from MySQL to Oracle database. It works great for text data, but fails when i am trying to transfer BLOB data. Even the Migration tool doesn’t work. Any idea / workaround to transfer image from MySQL to Oracle database will be great help.

    Thanks

  7. Santhosh K P Reply

    Hi all,

    I am having a table having one Blob column. For eg. i am having 10 records in the table. In which the data of 3 records in the blob column is corrupted. Is there any way that i can query these 3 records.

  8. I am trying to write a html file to a clob datatype. I’m able to click on the editor in SQL developer and paste the file. What I’m looking for is a code that can do this. I tried insert statement, but that has a limit on the characters.

    Please help me out.

    Thanks

  9. Hi ,
    Select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBDATA, 2000,1)) from shblobdata where UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBDATA, 3,1)) =’Y:\’;

    I am trying to pull out blob with where condition as above. but it fetches only first 150 rows and then it slows down. I have to kill the query and that gives me ORA-01002 Fetch out of sequence error.

  10. Hi , Im using a stored proc to fetch around 100 images stored as blob in a table. Proc is taking more than five mins to fetch all the rows. Please tell me if there are any ways to speed it up.

    • thatjeffsmith

      I would need to see your code. Have you tried the profiler to see where exactly your code is slow?

  11. Hi, is there a way to have the text within the select statement result? I always get BLOB for the BLOB fields in the table I’m running the select statement on! Im using SQL Developer and the database is DB2. Thank you.

    • thatjeffsmith

      you had me up until you said ‘DB2’

      In another world, if you were storing text in a LOB, you’d want to probably use CLOB vs BLOB. In an Oracle DB, if it’s a BLOB, you have to load an external editor, b/c the contents could be anything really..whereas CLOBs are always text.

  12. my blob image cannot open and i have this error message
    “l’image n’a pas pu être décodée à partir du flux de données binaire”

    • I have the same issue. BTW, the English message is “image could not be decoded from the binary stream”. I am going to write a small application to get the BLOB, it will take me about as much time as searching the answer to this error, there do not seem to be many sites documenting this SQL Developer feature and much less dealing with errors.

    • thatjeffsmith

      is it actually an image? if so, what type of image? try using the dialog to save the file locally and opening it with the associated editor instead of viewing it as a picture.

      or send me the file and i’ll test it on my side

    • hi good day can you please help me i have the same error

  13. John Taber Reply

    Thanks for this! The steps are a tab different on SQL Developer 4.0 but it was easy enough to figure out. I had no idea you could do this directly from SQL Developer. Awesome. 🙂

  14. Jeff, maybe you could help.. We are trying to migrate our Oracle 11g db to another hosting facility. We have a table with BLOBs that stores hundred thousands of images from our internal apps.

    We are doing an expdp and impdp on the new server, all imports correctly. But when we want to preview the BLOB photo, it never opens. Loads for ever. And one of the tables that has all the data never opens Data, freezes upon load..

    The new environment is exactly the same setup.

    Is there a special way to transfer BLOB data between databases?

    Thank you for any help!

    • thatjeffsmith

      No, datapump should ‘just work.’ When the session freezes, are are there any locks/blocks/wait events coming into play?

      I’m assuming you already looked at the data pump log from the import and export jobs.

    • Thank you Jeff for your fast reply.
      Logs show successful import, no errors there.

      Will try to monitor the session while opening the file.

    • Jeff, I think there is something wrong with the new db setup.. I cannot even access the Monitor Sessions.. It give me a Closed Connection Vendor code 17008 error when I try to open the Monitor Sessions

    • Jeff, it turned out that the firewall we have on the new environment was blocking the access to BLOB files.. I would never thought of it, was about to re-format the machine and start setup again.

      For anybody that hosts on GoDaddy dedicated servers with a Cisco firewall, you need to uncheck the 1521 port filtering. Even if you allow all connections from outside, it still has a reassembly limit that is being exceeded by the big binary files…

      Once I’ve turned it off, all photos were accessible!!

      Hope this helps somebody.

    • thatjeffsmith

      Weird! And definitely thanks for sharing!

      Also, had no idea that GoDaddy offered Oracle hosting.

    • Jeff, GoDaddy does not have it out of the box, we had to install it on the linux box by ourselves.

  15. Jon Wilkinson Reply

    Hi Jeff,

    This looks like a really cool feature.

    Should this work for blobs in MariaDb and MySQL over a jdbc connection ?

    I’m just trying to understand if its the way we have things set up or whether its an OracleDb only feature.

    • thatjeffsmith

      Most of what you’re going to see in SQL Developer is Oracle Database only. Some of the stuff is ‘free’ with standard JDBC, but this particular feature most likely isn’t one of those features.

      For the best MySQL support – the MySQL Workbench is the way to go.

  16. Hi Jeff,

    I have a question on how to mass download the BLOB.

    Hit the pencil button, open the Advance Editor, click on download link and save the data. Now that works, but for single record at a time.

    I want to do that for many records in single shot. Is there any way to do it?

    My BLOB data is Mainframe EBCDIC.

    Thanks,
    Aneesh.

    • Did a workaround for that. Exported the data as SQL Loader files (Separate file for each BLOB). I was able to translate/combine it later.

      Thanks,
      Aneesh.

  17. Excellent post! Just one doubt, if I want to save the work from all those click-click-click, does exist a shortcut key to display the BLOB image?

  18. Hey i have a problem storing an image into the BLOB field can u please help me out??

    • thatjeffsmith

      Nosheen, on the Columns page, how is the table column defined that you want to upload the BLOB data to?

      Also, what version of SQL Developer and Oracle are you working with?

    • thatjeffsmith

      then you need to copy the files down to the local system, or even better, run sqlldr ON the remote unix server – that way you don’t worry about the network traffic/latency slowing down the load process

  19. Hatem Khaled Reply

    Hi! I created the exact same table, but only with 2 columns: id and blob field. Loaded 5 images into it, and now I’d like to tag each image with separate words in Arabic that describe the stuff in the image. I’m working on a Story Picturing Engine that takes text in Arabic, searches the database for suitable images, and retrieves them. That’s why I need to annotate each image. Is it possible using SQL Developer? Thanks.

    • thatjeffsmith

      Add a column, say NVARCHAR2 (depending on what charset your db is using), to store your annotations in. Now, how do you want to view these, in a report, via the web (APEX), something else?

    • Hatem Khaled

      Haven’t decided on that. Suggestions?

    • thatjeffsmith

      That all depends on your skillset – what are you most comfortable with? Also, what are your requirements? Those two things should spell out what you use to build your solution.

  20. Peter Cliff Reply

    Just what I was looking for – thank you! Unfortunately, the load “button” is not active when you follow these steps on a set of query results, which can mean a lot of scrolling. You’d think it’d be easier to update BLOBs wouldn’t you? 🙂

    Thanks again.

    • thatjeffsmith

      query results – those are READ ONLY

      If you want to update the BLOBs, go to their source tables and use the grids there in the browser.

    • Peter Cliff

      Indeed. I’d kinda figured that out. However, if you want to update record id 10,503 of a table with 50,000 rows… That is a hell of a lot of scrolling! And before you say it, no – the filter didn’t work (at least not the way I was using it). Probably I’m just missing something there though. 🙂

    • thatjeffsmith

      How did you go about trying the filter? You should have been able to type ‘id=10503’ in the Filter input area and hit to take you to that record

    • Peter Cliff

      Think I found out why – response time seemed to make the filter not work. That’ll be the network I imagine… 🙂

      So again, thanks! Just what I was looking for!

    • JeffS
      JeffS

      Thanks Uwe! Let me know if you or your clients have any requests for topics to be covered in 2012!

Write A Comment