06 July, 2012

Playing with LOB Data Types

Lobs are the most difficult data type to store and retrieve in oracle database.

In this article, I am going to discuss extensively how to manipulate LOBs in Oracle database.
LOBs that are stored in the database itself like BLOB,CLOB,NCLOB.
BFILE which is stored outside the database as Operating System files.
BFILEs act as a pointer and store the location of the external OS files in database tables.


BFILE
First let us create database directory and database table which I will use it for demo
 CREATE DIRECTORY LOB_DIR AS 'D:\LOBS';  
 CREATE TABLE LOB_TABLE  
 (  
   LOB_ID   INTEGER,  
   lob_name  VARCHAR2 (100 BYTE),  
   BFILE_COL  BFILE  
 );  

You must create D:\LOBS folder if it doesn't exist and copy these file on it
1.jpg
2.jpg
3.jpg
file.txt

Let's insert little data in LOB_TABLE
 INSERT INTO LOB_TABLE (LOB_ID, LOB_NAME, BFILE_COL)  
    VALUES (1, '1ST PIC', BFILENAME ('LOB_DIR', '1.JPG'));  
 INSERT INTO LOB_TABLE (LOB_ID, LOB_NAME, BFILE_COL)  
    VALUES (2, '2ND PIC', BFILENAME ('LOB_DIR', '2.JPG'));  
 INSERT INTO LOB_TABLE (LOB_ID, LOB_NAME, BFILE_COL)  
    VALUES (3, '3RD PIC', BFILENAME ('LOB_DIR', '3.JPG'));  
 COMMIT;  

This BFILENAME function returns a BFILE locator for a physical LOB binary file.
Now, if we wish to display the contents of the table wee will write the below query
 SELECT * FROM LOB_TABLE;  

 
Note if you run it in any database editor like toad it will run OK but if you run it at sql plus it will return
 SP2-0678: Column or attribute type can not be displayed by SQL*Plus  

 
We can get the length of  OS files  Using the DBMS_LOB.GETLENGTH procedure
 SELECT dbms_lob.getlength(BFILE_COL) FROM LOB_TABLE;  

The output will be

 
Now, I have loaded the tables with data, and I come back after long period of time, 
I don’t remember the locations of the OS files.
In that case what should I do?
I will DBMS_LOB.FILEGETNAME procedure to get directory name and file name
 
I will create custom function to return file name which uses  DBMS_LOB.FILEGETNAME
 CREATE OR REPLACE FUNCTION GET_FILE_NAME (IN_BFILE BFILE)  
   RETURN VARCHAR2  
 IS  
   LC$DIR_NAME  VARCHAR2 (255);  
   LC$FILE_NAME  VARCHAR2 (255);  
 BEGIN  
   IF IN_BFILE IS NULL  
   THEN  
    RETURN NULL;  
   ELSE  
    DBMS_LOB.FILEGETNAME (IN_BFILE, LC$DIR_NAME, LC$FILE_NAME);  
    RETURN LC$FILE_NAME;  
   END IF;  
 END;  

 
Test GET_FILE_NAME function
 SELECT GET_FILE_NAME (BFILE_COL) FROM LOB_TABLE;  


The output will be
 
BLOB
First lets create table contains BLOB column
 CREATE TABLE SCOTT.LOB_TABLE2  
 (  
   LOB_ID   INTEGER,  
   lob_name  VARCHAR2 (100 BYTE),  
   BLOB_COL  BLOB  
 );  

After creating previous table I want to populate BLOB_COL column from operating system files
 DECLARE  
   L_BLOB  BLOB;  
   L_BFILE  BFILE;  
 BEGIN  
   L_BFILE := BFILENAME ('LOB_DIR', '1.jpg');  
   DBMS_LOB.OPEN (L_BFILE, DBMS_LOB.FILE_READONLY);  
   L_BLOB := DBMS_LOB.SUBSTR (L_BFILE, DBMS_LOB.GETLENGTH (L_BFILE), 1);  
   INSERT INTO LOB_TABLE2 (LOB_ID, LOB_NAME, BLOB_COL)  
     VALUES (1, '1st PIC', L_BLOB);  
   COMMIT;  
 END;  

After executing previous PLSQL block it inserted one record in LOB_TABLE2 table

CLOB
First let's create table contains CLOB column
 CREATE TABLE SCOTT.LOB_TABLE3  
 (  
   LOB_ID   INTEGER,  
   lob_name  VARCHAR2 (100 BYTE),  
   CLOb_COL  CLOB  
 );  
 
After creating previous table I want to populate CLOB_COL column from operating system files
 DECLARE  
   L_CLOB  BLOB;  
   L_BFILE  BFILE;  
 BEGIN  
   L_BFILE := BFILENAME ('LOB_DIR', 'file.txt');  
   DBMS_LOB.OPEN (L_BFILE, DBMS_LOB.FILE_READONLY);  
   L_CLOB := DBMS_LOB.SUBSTR (L_BFILE, DBMS_LOB.GETLENGTH (L_BFILE), 1);  
   INSERT INTO LOB_TABLE3 (LOB_ID, LOB_NAME, CLOB_COL)  
     VALUES (1, '1st Row', UTL_RAW.CAST_TO_VARCHAR2 (L_CLOB));  
   COMMIT;  
 END;  
 
Sometimes you want to convert CLOB data type to BLOB data type using below
 SELECT UTL_RAW.CAST_TO_RAW (CLOB_COL) FROM LOB_TABLE3;  
 
The output will be binary like below
 
If you want to query CLOB in query you should use DBMS_LOB.SUBSTR and specify how
 long of the string you will display as in my example I will show only the first 1000 character
 SELECT DBMS_LOB.SUBSTR (CLOB_COL, 1000, 1) FROM LOB_TABLE3;  
 
Kindly note that the text that I saved in file.txt file is "mahmoud ahmed mahmoud el-sayed" so the output
of the query will be
 

Thanks 
Recommended Post Slide Out For Blogger