Problem solve Get help with specific problems with your technologies, process and projects.

Working with binary large objects (BLOBs)

One reader asks how to create and retrieve a binary large object (BLOB), and SQL expert Karen Morton gives some comprehensive advice and tips.

How do I insert and retrieve a BLOB data?

I'd recommend that you read the documentation location in the "Application Developer's Guide - Large Objects".

Here are some links to the full guide and specifically to Chapter 14 which shows examples of inserting and reading BLOBs:

Table of Contents - http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/toc.htm

Chapter 14 - http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_lob_ops.htm#i1024873

To get you started here is an example of the steps to follow in order to take external files and load them into a BLOB column in a database table.

Step 1: Create a Directory that points to where BLOB resides.
create or replace directory blob_dir as '/oradata/blobs';

Step 2: Grant read permission to the user who work with this directory.
grant read on directory blob_dir to kmorton;

Step 3: Create the Table which holds lob object.
-- the storage table for the image file
CREATE TABLE my_blobs (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name or description
fname VARCHAR2(30), -- file name
fblob BLOB); -- blob file

Step 4: Create a procedure that will be used to insert BLOB objects.

-- create the procedure to load the blob file
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS

src_file BFILE;
dst_file BLOB;

src_file := bfilename('BLOB_DIR', pfname);

-- insert a NULL record to lock
INSERT INTO my_blobs
(dname, sname, fname, fblob)
(pdname, psname, pfname, EMPTY_BLOB())
RETURNING fblob INTO dst_file;

-- lock record
SELECT fblob
INTO dst_file
FROM my_blobs
WHERE dname = pdname
AND sname = psname
AND fname = pfname

-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

-- determine length
lgh_file := dbms_lob.getlength(src_file);

-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

-- update the blob field
UPDATE my_blobs
SET fblob = dst_file
WHERE dname = pdname
AND sname = psname
AND fname = pfname;

-- close file
END load_file;

Step 5: Execute the Procedure.

SQL> exec load_blob('BLOB_DIR','Head shot photo','km_pic.png');
PL/SQL procedure successfully completed.

You can now verify the original object matches the database object by checking the sizes.

Step 6: From the OS check the BLOB size.

SQL> !ls -l /oradata/blobs/km_pic.png
-rwxr-xr-x 1 oracle oinstall 21150 Jan 6 01:55 /oradata/blobs/km_pic.png

Step 7: From Oracle check the BLOB size.
1 declare
2 a blob;
3 begin
4 select fblob into a from my_blobs;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /
PL/SQL procedure successfully completed.

Read the documentation for more examples and details on using BLOB data. Good luck!

This was last published in January 2012

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.








  • How do I size a UPS unit?

    Your data center UPS sizing needs are dependent on a variety of factors. Develop configurations and determine the estimated UPS ...

  • How to enhance FTP server security

    If you still use FTP servers in your organization, use IP address whitelists, login restrictions and data encryption -- and just ...

  • 3 ways to approach cloud bursting

    With different cloud bursting techniques and tools from Amazon, Zerto, VMware and Oracle, admins can bolster cloud connections ...