Handling Large Objects (LOB) – Oracle Database 10g

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);

Table created.

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’;

Directory created.

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.

    l_blob    blob;
    l_bfile    bfile;
    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.
load data
infile photos.txt
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$ pwd 
bash-2.05$ ls -lrt
total 576
-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 – 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
  2  ;


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_id   NUMBER,
        bfile_loc BFILE,
        doc_title VARCHAR2(255),
        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’;

Directory created.

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         BFILE;
  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));    



END load;

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
    (title VARCHAR2(40),
    author VARCHAR2(40),
    text CLOB,
    author_pic BLOB);

Table created.


Step 2) Insert some data

  2  (‘My Book’,’Advait’,EMPTY_CLOB(), EMPTY_BLOB());

1 row created.

Step 3) Update column using PL/SQL procedure

my_text_handle CLOB;
my_buffer VARCHAR2(4000);
my_add_amt NUMBER := 0;
my_offset INTEGER := 1;
my_buffer := ‘This is my book’;    
my_add_amt := length(my_buffer); 
SELECT text  
INTO my_text_handle
FROM book
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.

38 thoughts on “Handling Large Objects (LOB) – Oracle Database 10g

  1. 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.

  2. unload table data into flat file, including blob data.. using pl/sql procedure….

    kindly post the solution for the same

  3. 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)?

  4. Hi,
    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

  5. Dear Sir/Madam,
    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 =deviprasad.muggulla@qsoftindia.com

    1. Hi Prasad,

      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.


      Ghulam, UK

  6. Hi,

    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.

    Ram Paleti

  7. Hi,
    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???

  8. 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

  9. Hi

    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?

  10. Excellent and useful information. this is what i was looking for. Google and wonderful persons making many IT profession life.

    Thanks Once again

  11. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s