Large Objects (LOB)
Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.
BLOB – Binary Large Object
Stores any kind of data in binary format. Typically used for multimedia data such as images, audio, and video.
CLOB – Character Large Object
Stores string data in the database character set format. Used for large strings or documents that use the database character set exclusively. Characters in the database character set are in a non-varying width format.
NCLOB – National Character Set Large Object
Stores string data in National character set format. Used for large strings or documents in the National character set. Supports characters of varying width format.
BFILE – External Binary File
A binary file stored outside of the database in the host operating system file system, but accessible from database tables. BFILEs can be accessed from your application on a read-only basis. Use BFILEs to store static data, such as image data, that does not need to be manipulated in applications. Any kind of data, that is, any operating system file, can be stored in a BFILE. For example, you can store character data in a BFILE and then load the BFILE data into a CLOB specifying the character set upon loading.
Following are the examples, which I tried my self with oracle database 10g on Linux installation and pasted the contents. I have also put the neccessary comments for your understanding. Kinly let me know in case you have any doubts.
Storing a image file in oracle database table using DBMS_LOB package
Step 1) create a table
SQL> create table loadalbum
2 (name varchar2(100),
3 image blob);
Step 2) Create a Directory object in database which will point to operating system directory.
SQL> CREATE OR REPLACE DIRECTORY DOCUMENTS AS ‘/dy/oracle/product/db10g/photo’;
Step 3) Use the following procedure to insert a BLOB (image file) into database.
Note: The file (Sunset.jpg), which we are inserting into table should be present in DOCUMENTS directory location (/dy/oracle/product/db10g/photo) created above.
insert into loadalbum values ( ‘Sunset’, EMPTY_BLOB() ) // First create a Emply binary large object
returning image into l_blob; // and get a reference
l_bfile := bfilename( ‘DOCUMENTS’, ‘Sunset.jpg’ ); // Get the pointer to a file in directory
dbms_lob.fileopen( l_bfile ); // Open file
dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
SQL> select count(*) from loadalbum;
Doing bulk upload of images to database.
You can use SQL*Loader utility to bulk upload regular text data. Same utility can be used to bulk upload images as well. Follow below steps to bulk up load the images
Step 1) Create a table
CREATE TABLE photoalbum (photolob BLOB);
Step 2) Create a file (example photos.txt ) which will contain list of images to be uploaded.
bash-2.05$ cat photos.txt
Step 3) create a control file required by SQL*Loader to upload data. Create new file called loadphotos.ctl and insert following content into it.
into table photoalbum
(ext_fname filler char(200),
photolob lobfile(ext_fname) terminated by EOF)
The meaning of above code is
“please load the data listed in the file photos.txt into a table called photoalbum. The data will be loaded into the column of that table called ‘photoblob’ and has lobfile characteristics… that is, it’s binary data. Expect the file names for the binary files being loaded to be up to 200 characters in length. When you reach the end of the list of photos, terminate the load process”.
Step 4) Run SQL*Loader
Please note that photos.txt is used in control file and we are not giving absolute path, but relative path. So control file and photos.txt should be in same directory. And so also all images. See below.
bash-2.05$ ls -lrt
-rw-r–r– 1 db10g oinstall 105542 Jun 2 23:43 Winter.jpg
-rw-r–r– 1 db10g oinstall 83794 Jun 2 23:43 Water_lilies.jpg
-rw-r–r– 1 db10g oinstall 71189 Jun 2 23:43 Sunset.jpg
-rw-r–r– 1 db10g oinstall 28521 Jun 2 23:43 Blue_hills.jpg
-rw-r–r– 1 db10g oinstall 127 Jun 2 23:46 loadphotos.ctl
-rw-r–r– 1 db10g oinstall 54 Jun 2 23:48 photos.txt
bash-2.05$ sqlldr system/manager control=loadphotos.ctl
SQL*Loader: Release 10.2.0.1.0 – Production on Sat Jun 2 23:48:21 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Commit point reached – logical record count 4The above message shows that data is uploaded correctly. You can check the log file generated by SQL Loader at same location.SQL> select count(*) from photoalbum
Uploading Word Document to oracle database
You can use database tables for storing work documents as well. Please follow the below steps for doing the same
Step 1) Create table
CREATE TABLE my_docs
doc_blob BLOB DEFAULT EMPTY_BLOB() ); // Default value will be empty binary large object
Step 2) Create directory object
SQL> CREATE OR REPLACE DIRECTORY DOC_DIR AS ‘/dy/oracle/product/db10g/doc’;
Step 3) Create a procedure for uploading the file. Here inputs will be file_name and file_id.
Create or replace PROCEDURE load (in_doc IN VARCHAR2, in_id IN NUMBER) IS
temp_blob BLOB := empty_blob();
bfile_loc := BFILENAME(‘DOC_DIR’, in_doc);
INSERT INTO my_docs (doc_id, bfile_loc, doc_title) VALUES (in_id, bfile_loc, in_doc);
SELECT doc_blob INTO temp_blob FROM my_docs WHERE doc_id = in_id FOR UPDATE;
DBMS_LOB.LOADFROMFILE(temp_blob, bfile_loc, dbms_lob.getlength(bfile_loc));
Step 4) Suppose I want to uplaod a .doc file present in the DOC_DIR directory (/dy/oracle/product/db10g/doc at OS level) created above.
The doc name is advait.doc.
Execute the above procedure as given below.
SQL> exec load(‘advait.doc’, 1);
PL/SQL procedure successfully completed.
SQL> select count(*) from my_docs;
The docs advait.doc will be saved in database table.
Updating LOB column of database table
Step 1) Create a table
CREATE TABLE book
Step 2) Insert some data
SQL> INSERT INTO book VALUES
2 (‘My Book’,’Advait’,EMPTY_CLOB(), EMPTY_BLOB());
1 row created.
Step 3) Update column using PL/SQL procedure
my_add_amt NUMBER := 0;
my_offset INTEGER := 1;
my_buffer := ‘This is my book’;
my_add_amt := length(my_buffer);
WHERE title = ‘My Book’ FOR UPDATE;
DBMS_LOB.WRITE(my_text_handle, my_add_amt, my_offset, my_buffer);
PL/SQL procedure successfully completed.
SQL> select text from book;
This is my book
This can hold 4G of characters.
39 thoughts on “Handling Large Objects (LOB) – Oracle Database 10g”
good one 🙂 i really like it!
Awesome Post. My compliments to the author.
It is very good one
interestinging article how to get data lob into oracle the question remains how to easily get it out /////
it is very helpful to me
Excellent!.Congrats to author.Highly useful.
I want to insert bulk images into mysql, i want same coding for that.
Wonderfull…..am more thirsty now
How to use CLOB column in Oracle Forms 6i
plz tell me how to update CLOB column in Oracle which has xml data?>????
How to use CLOB column in Oracle Forms 6i on database 10g? Forms works fine ( table with CLOB) on database 8i, but on database 10g forms crach( ORA805.dll). It works OK if tables have only one record. Thanks in advance.
Update column using PL/SQL procedure
This section for updating the CLOB column is not working.
Kindly post the solution for the same
unload table data into flat file, including blob data.. using pl/sql procedure….
kindly post the solution for the same
Hi Advait Deo,
I have added a column of type BLOB to an existing table which has got more than 32 lakhs rows.
Now, how to update that newly added column (BLOB type)?
Hi, how load on db all files of my directory?
Very userful procedure.
Could you please tell me why this error is pop up when i try to insert character larger than 32000 into clob column
“URL encoded SQL length exceeds allowed 32k limit”
Thanks and Regards
Can you help how to rotate images in forms10g,
in forms6i we have property showpalette for images ,that not available in forms10g.
Then how to rotate images in forms 10g
Can you help the preceddings.
I am very thankful to you.
Send to my mail-id =firstname.lastname@example.org
Did you find some clue on how to rotate photo / images in forms 10g?
I got the same requirement and will appreciate any help on this please.
Thanks it was sufficient to finish my requirement
Nice one 🙂 🙂
this article is excellent. We need to load all the xml files from a linux directory to the database .would you please provide the procedure hoe can we do that.
This article is excellent to learn about loading multimedia data into oracle database..
Can any one tell me how to play the video or audio which is available in my oracle database???
This very userfull information for my reuirement, excellent good job.
Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.
thank u thank u
Hi. thanx for that info. just to ask if dbms_lob is applicable in forms 6i because im now using dbms_lob.wrtite in the program that im developing but when i run it, it terminates the program when it meet the dbms_lob.write procedure but when i try it in oracle sql*plus it run and give the output that i need. thanx for reply in advance
im using oracle forms 6i and the cversion of my oracle sql*plus is 8.0
whats the difference? than again
Thanks for that code. I could insert a blob value. Actually it is a jpg file. Now if I want to read it how can I do that?
How can I retrieve a blob data? How can I check whether these lob columns are populated in a table using a select statement? Or display the lob locator in a pl/sql code?
How can I convert a blob column value into a bfile so that the blob data is now available as a OS file? ( that is reverse of the insert you have shown)
Otherwise what is the use of storing an image file as a blob data?
Interesting topic and nice posting. Thanks for sharing.
thanks a lot.
Excellent and useful information. this is what i was looking for. Google and wonderful persons making many IT profession life.
Thanks Once again
how to video/image stored in oracle database 10g
very.. informative! thank you! but has i can load the procedure in oracle 6i forms??
very informative! thank you! but how i can load the procedure in oracle 6i forms??
This is really good post if the data is equal to or less than 4000 characters.
if it exceeds than this limit, then what to do?
and how to update the CLOB data?
super document yar