Playing with Oracle Lobs

dwaipayan19861's picture

LOBs deal with unstructured data, the ones which are the most difficult to store and retrieve in a relational database.

In this article, I am going to discuss extensively and manipulate the ORACLE

* LOBs that are stored in the database itself like BLOB,CLOB,NCLOB ,and

* LOBs like BFILE which are Stored outside the database as Operating System files.

First let us create a directory :

create or replace directory my_dir as 'D:\pics';
Directory created.

BFILE:
BFILEs act as a pointer and store the location of the external OS files in database tables.

1. POPULATING BFILE COLUMNS IN DATABASE TABLES:
Now we insert a row in it.

Insert into lob_test values (1,'dwaipayan',bfilename('MY_DIR',' pic1.jpg'));

This bfilename function returns a BFILE locator for a physical LOB binary file.
Now, if we wish to display the contents of the table:

select * from lob_test;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

We get an error as we cannot display the contents of a bfile like this.

Now, let us insert another row in the table:

insert into lob_test values (2,'shiba',bfilename('MY_DIR','9.jpg'));
1 row created.

Using the DBMS_LOB.GETLENGTH procedure we can get the lengths of the OS files as follows:

select dbms_lob.getlength(PIC) from lob_test;
DBMS_LOB.GETLENGTH(PIC)
---------------------------
180496
15032

Note: PIC is a BFILE column

2. Locating the files in OS from a BFILE:

Now, say I have loaded the tables with data, and I come back after 3 months and I don’t remember the locations of the OS files with which I had loaded the data into the tables.
In that case what should I do…
Simply, we take the help of DBMS_LOB.FILEGETNAME procedure.

CREATE or replace FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
DIR_ALIAS VARCHAR2(255);
FILE_NAME VARCHAR2(255);
BEGIN
IF bf is NULL
THEN
RETURN NULL;
ELSE
DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
RETURN FILE_NAME;
END IF;
END;

select get_dir_name(pic) from lob_test;

GET_DIR_NAME(PIC)
--------------------------------------------------------------------------------
pic1.jpg
9.jpg

3. OPENING A BFILE AND COPYING IT TO ANOTHER OS LOCATION

File type: image (jpg)

CREATE OR REPLACE procedure imagecopy
AS
h1 bfile;
h2 utl_file.file_type;
len integer;
strt integer;
buf raw(32767);
BEGIN
h2 := utl_file.fopen('MY_DIR','copyof9.jpg','Wb');
select pic into h1 from lob_test where empno=1;
dbms_lob.open(h1,dbms_lob.file_readonly);
strt:=1;
select dbms_lob.getlength(h1) into len from dual;
while strt<=len
loop
buf := dbms_lob.substr(h1,10000,strt) ;
utl_file.put_raw(h2,buf,TRUE);
strt := strt+10000;
end loop;
utl_file.fclose(h2);
END;
/

So, the image file that was pointed to by the BFILE in lob_test table with empno=1, is now copied to the new location in the OS under the directory ’MY_DIR’ as 'copyof9.jpg'.
Note: Here we have used utl_file package to open the database file where we are going to write the data. The mode in which the 'copyof9.jpg' file is opened is ‘Wb’ because we are going to write binary data in it.
Utl_file.put_raw is used to write raw data into the file
Besides these, we have used the dbms_lob.substr function.It’s syntax is a bit different from the ordinary substr function. In dbms_lob.substr, the requested substring length comes first, followed by the offset.

BLOB :
We , first create a table test_blob containing a blob column ‘DOC’ :

create table test_blob (empno number,empname varchar2(30),doc blob);

Table created.

1. Populating Blob column in database from OS file:

create or replace procedure blobfromfile

as
blob_buf blob;
h1 bfile;
begin
h1 := bfilename('MY_DIR','9.jpg');
dbms_lob.open(h1,dbms_lob.file_readonly);
blob_buf := dbms_lob.substr(h1,dbms_lob.getlength(h1),1);
insert into test_blob values (1,'dwaipayan',blob_buf);
commit;
end;

select count(*) from test_blob;

COUNT(*)
----------
1

CLOB :
We , first create a table test_clob containing a clob column ‘ABOUT_ME’ :

create table test_clob (empno number,about_me clob);

Table created.

insert into test_clob values (1,empty_clob());

1 row created.

1. Populating Clob column in database from OS file:

CREATE OR REPLACE procedure CLOB_POPULATE
AS
CLOB_BUF blob;
h1 BFILE;
BEGIN
h1 := BFILENAME('MY_DIR','t3.txt');
dbms_lob.open(h1,dbms_lob.file_readonly);
clob_buf := dbms_lob.substr(h1,dbms_lob.getlength(h1),1) ;
update test_clob set about_me=utl_raw.cast_to_varchar2(clob_buf) where empno=1;
commit;
end;

select substr(about_me,1,100) FROM test_clob;

SUBSTR(ABOUT_ME,1,100)
--------------------------------------------------------------------------------
ooo
hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
ooooooooooooooooooooooooooooooooo
hhh

2. CONVERTING CLOB TO BLOB :

insert into test_blob
2 select 2,'sinha',utl_raw.cast_to_raw(about_me) from test_clob where empno=1;

1 row created.

Displaying The raw data:

select dbms_lob.substr(doc,100,1) from test_blob where empno=2;

DBMS_LOB.SUBSTR(DOC,100,1)
--------------------------------------------------------------------------------
0D0A6F6F6F0D0A686868686868686868686868686868686868686868686868686868686868686868
0D0A6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F0D0A686868
6868686868686868686868686868686868686868

ABOUT AUTHOR

Linkedin site: http://in.linkedin.com/pub/dwaipayan-de/21/20/29b

WEBSITE : http://www.databasefundas.blogspot.com/