Q

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
CREATE OR REPLACE PROCEDURE load_blob (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;

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

-- insert a NULL record to lock
INSERT INTO my_blobs
(dname, sname, fname, fblob)
VALUES
(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
FOR UPDATE;

-- 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
dbms_lob.fileclose(src_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> /
21150
PL/SQL procedure successfully completed.

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

This was first published in January 2012

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchSAP

SearchSQLServer

TheServerSide

SearchContentManagement

SearchFinancialApplications

Close