ThatJeffSmith

SQL Developer Quick Tip: BLOBs and Images

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.