Loading LOB (BLOB) data into tables

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Loading LOB (BLOB) data into tables

Oracle Database Tips by Donald Burleson

QUESTION: I want to load some LOB data types (BLOBs) into an Oracle table. What are my table definitions and data loading options?

ANSWER:

To load external LOB data into internal LOB storage using PL/SQL alone you must first use a table containing BFILE locators for the files to allow Oracle to access them.

create table
graphics_table
(

bfile_id number,

bfile_desc varchar2(30),

bfile_loc bfile,

bfile_type varchar2(4)
)

TABLESPACE
appl_data

storage
(initial 1m next 1m pctincrease 0);

Notice in the above code that no LOB storage clause is specified. This is because all that is stored in the database is a locator value for the BFILE consisting of an internal DIRECTORY specification and a file name. The BFILE locators are loaded into the table using the BFILENAME function and a standard insert statement.

An example of this process is shown here:

SQL> INSERT INTO graphics_table

2 VALUES(4,'April Book of Days Woodcut',bfilename('GIF_FILES','APRIL.JPG'),'JPEG');

1 row created.

SQL> INSERT INTO graphics_table

2 VALUES(8,'August Book of Days Woodcut',bfilename('GIF_FILES','AUGUST.JPG'),'JPEG');

1 row created.

SQL> INSERT INTO graphics_table

2 VALUES(13,'Benzene Molecule',bfilename('GIF_FILES','BENZNE.GIF'),'GIF');

1 row created.

My other notes on loading LOB data includes: