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.
Persistent Storage Solutions 