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…
