Working with LOB in Oracle (series 01)

One of my customers wanted to manage, store and retrieve BLOB in Oracle 11g database. They enquired me about the available options and evaluate effective feasibility.
Though Oracle DB supported BLOB/CLOB for a long time (since 8i), there is still apparent some mystery in achieving this.
Before we go to 11g new features, I wanted to demonstrate the “traditional” BLOB management in Oracle DB.
In this BLOG, I’ll demonstrate how to load a file as BLOB in Oracle 11g.
Environment setup
I am using Oracle 11g (11.1.0.7) instance hosted in AWS instance. We’ll use a table created under HR schema. In this posting I’ll use only SQL and PL/SQL constructs to store and retrieve BLOB objects in the table.
The first example is with BLOB column as follows…
SQL> select * from v$version where rownum < 3;
BANNER
—————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
PL/SQL Release 11.1.0.7.0 – Production
SQL> select user, sysdate from dual;
USER                           SYSDATE
—————————— ———
HR                             17-AUG-12
SQL>
A BLOB can be stored into column that is defined as BFILE, which will store the file outside the database. We’ll demonstrate this in next posting.
Create a table with BLOB object as one of the column.
SQL> create table tblob (cid int, bcol blob, ts timestamp);
Table created.
Now we are ready to load BLOB in the database table and retrieve using SQL, PL/SQL. DBMS_LOB is Oracle supplied package that can be used to handle LOB (BLOBs & CLOBs).
I have a text file on the server under /tmp directory that I’ll load into the table and retrieve. In my example the text file should ideally be loaded into a CLOB column.
Here is the file that will be loaded into BLOB column.
$ ll
total 4
-rw-r–r– 1 oracle oinstall 232 Aug 17 18:58 testblob.txt
oracle@domU-12-31-39-00-7C-94:[/tmp/blob]
Since, SQL/PL-SQL run within the database on the server, the file (BLOB) and the location must be on the server and accessible to database server. This can be accomplished by creating a directory in database and granting required privilege to the user. This can be accomplished by using a directory object in database.
Create directory:
I’ll create a directory in database and grant read privilege to the HR user.
$ sqlplus  / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Sat Aug 18 08:01:25 2012
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create or replace directory myblobdir as’/tmp/blob’;
Directory created.
SQL> grant read on directory myblobdir to hr;
Grant succeeded.
SQL>
SQL> connect hr
Enter password:
Connected.
SQL> select * from all_directories;
OWNER    DIRECTORY_NAME    DIRECTORY_PATH
——– —————– ——————————————–
SYS      MYBLOBDIR         /tmp/blob
SQL>
I’ll create a PL/SQL procedure to load the file into BLOB.
CREATE OR REPLACE PROCEDURE load_file is
  src_lob  BFILE := BFILENAME(‘MYBLOBDIR’, ‘testblob.txt’);
  dest_lob BLOB;
BEGIN
  INSERT INTO tblob VALUES(2, EMPTY_BLOB(),systimestamp)
     RETURNING bcol INTO dest_lob;
  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);
  COMMIT;
END;
We can use SQLPlus to access the LOB from tables.
SQL> desc tblob;
 Name                                Null?    Type
 ———————————– ——– ———————-
 CID                                          NUMBER(38)
 BCOL                                         BLOB
 TS                                           TIMESTAMP(6)
SQL> select count(*) from tblob;
  COUNT(*)
———-
         0
SQL> select * from tblob;
no rows selected
SQL> select dbms_lob.getlength(bcol) from tblob;
no rows selected
SQL>
Execute the procedure to load the file.
SQL> exec load_file;
PL/SQL procedure successfully completed.
SQL> select count(*) from tblob;
  COUNT(*)
———-
         1
SQL> select dbms_lob.getlength(bcol) from tblob;
DBMS_LOB.GETLENGTH(BCOL)
————————
                     232
SQL>
Note The size (length) is matching indicating the file has been loaded.
SQL> !ls -ltr;
total 8
-rw-r–r– 1 oracle oinstall 232 Aug 17 18:58 testblob.txt
-rw-r–r– 1 oracle oinstall 471 Aug 18 08:35 load_file.sql
SQL>
Of course, since we loaded text file as BLOB, SQLplus retrieval displays the HEX representation of the data. The data text can be retrieved using DBMS_LOB conversion function.
SQL> select * from tblob;
       CID BCOL                                                                                                                                                             TS
———- —————————————————————————————————————————————————————- —————————-
         2 54686973206973206120544558742066696C652063726561746564207573696E6720564920656469746F7220756E646572206C696E7578204F532E0A4F66636F75727365207468697320636F756C6420 18-AUG-12 08.43.48.247162 AM
SQL>
SQL> select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(bcol,4000,1)) t from tblob;
T
——————————————————————————–
This is a TEXt file created using VI editor under linux OS.
SQL>
In the next posting I’ll demonstrate the use of CLOB, conversion between CLOB and BLOB, and explote additional functionality of DBMS_LOB package.
Till then Bye…
References
Oracle 11g (11.2) documentation managing BLOB using various programming languages and environment can be found here…
·         Working with LOB and BFILEs in Oracle (using JDBC)
·         PHP example for loading image and displaying the image:
·         There is a supplied package DBMS_LOB that provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs.
·         ORAFAQ has a very simple introduction for loading BLOB in Oracle using DBMS_LOB package.
·         Check out Arup Nanda’s post about new features on SecureFiles LOB management in 11g here…

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s