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.
I am using Oracle 11g (220.127.116.11) 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…
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.
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.
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.
I’ll create a directory in database and grant read privilege to the HR user.
I’ll create a PL/SQL procedure to load the file into BLOB.
We can use SQLPlus to access the LOB from tables.
Execute the procedure to load the file.
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.
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…
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
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…