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