Answer

Working with binary large objects (BLOBs)

How do I insert and retrieve a BLOB data?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: