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.

declare
    l_blob    blob;
    l_bfile    bfile;
begin
    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 );
end;

SQL> select count(*) from loadalbum;

  COUNT(*)
———-
         1

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
Winter.jpg
Water_lilies.jpg
Sunset.jpg
Blue_hills.jpg

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 
/dy/oracle/product/db10g/photo
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 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
  2  ;

  COUNT(*)
———-
         4

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;
BEGIN
  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.OPEN(bfile_loc, DBMS_LOB.LOB_READONLY);  
  DBMS_LOB.OPEN(temp_blob, DBMS_LOB.LOB_READWRITE); 
  DBMS_LOB.LOADFROMFILE(temp_blob, bfile_loc, dbms_lob.getlength(bfile_loc));    

  DBMS_LOB.CLOSE(temp_blob);

  DBMS_LOB.CLOSE(bfile_loc);

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

  COUNT(*)
———-
         1

The docs advait.doc will be saved in database table.

Updating LOB column of database table

Step 1) Create a table
    CREATE TABLE book
    (title VARCHAR2(40),
    author VARCHAR2(40),
    text CLOB,
    author_pic BLOB);

Table created.

SQL>

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

DECLARE
my_text_handle CLOB;
my_buffer VARCHAR2(4000);
my_add_amt NUMBER := 0;
my_offset INTEGER := 1;
BEGIN
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);
COMMIT;
END;

PL/SQL procedure successfully completed.

SQL>

SQL> select text from book;

TEXT
——————————————————————————–
This is my book

This can hold 4G of characters.

---

Possibly related posts: (automatically generated)

• Installing Oracle Data Mining Manually