SQL Developer Quick Tip: BLOBs and Images

thatjeffsmith SQL Developer 65 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 65

  1. 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.

    1. thatjeffsmith Post
      Author
      1. 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?

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

    1. thatjeffsmith Post
      Author
  3. 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

    1. thatjeffsmith Post
      Author
      1. 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

  4. 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.

    1. thatjeffsmith Post
      Author
  5. 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

  6. 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.

    1. thatjeffsmith Post
      Author
  7. 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

    1. thatjeffsmith Post
      Author
  8. 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.

    1. thatjeffsmith Post
      Author
  9. 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.

    1. thatjeffsmith Post
      Author
  10. 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.

    1. thatjeffsmith Post
      Author

      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.

  11. 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”

      1. 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.

        1. thatjeffsmith Post
          Author

          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

          1. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
  12. 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. 🙂

  13. 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!

    1. thatjeffsmith Post
      Author

      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.

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

        Will try to monitor the session while opening the file.

        1. 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

      2. 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.

        1. thatjeffsmith Post
          Author
  14. 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.

    1. thatjeffsmith Post
      Author

      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.

  15. 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.

    1. thatjeffsmith Post
      Author
      1. 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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      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?

    1. thatjeffsmith Post
      Author

      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

  16. 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.

    1. thatjeffsmith Post
      Author

      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?

        1. thatjeffsmith Post
          Author
  17. 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.

    1. thatjeffsmith Post
      Author
      1. 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. 🙂

        1. thatjeffsmith Post
          Author
          1. 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!

          2. thatjeffsmith Post
            Author
    1. JeffS Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *