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
- On the ‘Local Data’ panel, use the ‘Load’ hyperlinked text
- Navigate to your file, select and click ‘Open’
- COMMIT your transaction
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.
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.
80 Comments
Hello there.
¿How can I show a picture stored as a blob in a table in an SQLDeveloper HTML report?
I don’t know that you can.
I am having a problem defining the External Editor in the Preference section for version 22.2.1.234 Build 234.1810 in my Windows env.
There is no option for adding or modifying any external editor in this version. Could it be new changes introduced by this version? My second attempt was to define a new file type .pdf, in the File Types session. However, there is neither pfd file type nor a button to modify the Default Editor to associate with the new .pfd file type.
Here is my finding. Viewing pdf file works for an older version, Version 21.4.3.063 Build 063.0100.
I am facing some issues when I upload files
Like…?
Load Option is not visible in oracle
I’m going to need more information than that if I’m going to help you.
I looked at the post, tried it (without using Cart), and it does work to get the BLOBs out to separate files. However, they all need renaming to have the original PDF filename. That’s stored in another column of the Report_Cards table, so I can include that and the BLOB column to get a *.ldr file with two fields – the original filename and the unloaded BLOB file name. I suppose I can use that to create a script to rename all the unloaded files to their original *.pdf names, but is there another way? Can this be scripted with sdcli or sqlcl?
SD is a wonderful tool and I appreciate what it does very much. We use it extensively for DBA work.
Thanks.
Jack
You just need this, but the opposite.
I’ll see if I can do the sqlcl + java scripting. Otherwise, I’ll need help from one of the Java heads at work.
Thanks.
Jack
I’m re-posting this as a Comment to the original Quick Tip, not a reply to an old comment.
You said “…use the ‘Download’ method instead. Save it to a file…” which is the ONLY way I’ve found to write out a >32KB BLOB to an OS file. I’m now on SD 19.2 and very much want to script this function to unload 80,000+ PDFs of about 1MB each to OS files. This for the purpose of converting them to BFiles. BTW, we are a K12 school district and these are student report cards – a new set get produced every 6 to 9 weeks.
If you know another way to UNload large BLOBs to OS files, I’d love to hear it. Most everything I’ve come across, including DBMS_LOB is almost exclusively on how to Upload BLOBs from OS files or convert BFiles to Internal BLOBs.
I was hoping maybe sdcli or sqlcl could do it, but can’t see how unless we use sqlcl as a way to execute Java.
Thanks.
Jack
You saw my post on the Cart and using the sqlldr option? That’ll unload the blobs as files.