How to operate lob by using DBMS_LOB in 11g

–Test version: 11.1.0.6

–The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs.
–DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs;it provides read-only operations for BFILEs
Temporary LOB
-Stored in temporary tablespaces, deleted at the end of the session by default
Internal LOB
-LOB columns are defined in a table
-Use DML to initialize or populate the locators in the LOB columns
External LOB
-BFILE is the only type, can only read data in oracle.
-Using DIRECTORY and filename to create a LOB locator


–Temporary LOB

–create test tables
create table t1 (id number, chunk_value1 varchar2(4000),chunk_value2 varchar2(4000));
create table t2 (id number, chunk clob);

create or replace function f_getclob(p_chunk1 in varchar2,p_chunk2 in varchar2)
return clob
as
v_chunk clob;
v_temp varchar2(8000);
begin
dbms_lob.createtemporary(lob_loc=> v_chunk, cache=> true, dur=> dbms_lob.session);
v_temp := p_chunk1||p_chunk2;
dbms_lob.write(v_chunk,length(v_temp),1,v_temp);
–dbms_lob.freetemporary(v_chunk);
–as we need to return it, we cannot free it now.let it free automatically
return v_chunk;
end;
/

–insert data
insert into t1 values(1,lpad(‘*’,4000,’*’),’test’);
insert into t2 select id,f_getclob(chunk_value1,chunk_value2) from t1;
SQL> select id,length(chunk) from t2;
ID LENGTH(CHUNK)
———- ————-
1          4004

SQL> select * from V$TEMPORARY_LOBS;

SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
———- ———- ———— ————-
231          0            0             0

–check tempsize used by temporary LOB
select s.username, s.sid, u.tablespace, u.contents, u.segtype, round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr and u.contents = ‘TEMPORARY’ and u.segtype=’LOB_DATA’
order by MB DESC ;
USERNAME                              SID TABLESPACE
—————————— ———- ——————————-
CONTENTS  SEGTYPE           MB
——— ——— ———-
SCOTT                            231 TEMP
TEMPORARY LOB_DATA           1


–Internal LOB

create table ti1(id number unique, chunk clob);
create or replace procedure p_insertlob(p_id in number,p_chunk1 in varchar2,p_chunk2 in varchar2) as
v_clob clob;
begin
–insert
insert into ti1 values (p_id, empty_clob()) returning chunk into v_clob;
dbms_lob.write(v_clob,length(p_chunk1),1,p_chunk1);
commit;
–update
select chunk into v_clob from ti1 where id=p_id for update;
dbms_lob.writeappend(v_clob,length(p_chunk2),p_chunk2);
commit;
end;
/

exec p_insertlob(1,lpad(‘$’,32767,’$’),lpad(‘*’,32767,’*’));

select id,dbms_lob.getlength(chunk) from ti1;

ID DBMS_LOB.GETLENGTH(CHUNK)
———- ————————-
1                     65534

Note:if you have functional and domain indexes on the LOB column, using dbms_lob.open() before write and dbms_lob.close() before commit are good for performance.
as the close() operation will trigger update on indexes. If no open() and close(), the indexes are updated at the same time doing update.


–External LOB

–function to read file from a giving diretory and file name
create or replace function p_get_elob(p_directory varchar2, p_filename varchar2)
return clob
as
v_file bfile;
v_flag integer :=0;
v_length number :=0;
v_clob clob;
v_src_offset integer :=1;
v_des_offset integer :=1;
v_lang_context integer :=0;
v_waring integer;
begin
dbms_lob.createtemporary(v_clob, true, dbms_lob.session);
v_file := bfilename(p_directory,p_filename);
v_flag := dbms_lob.fileexists(v_file);
if v_flag =0  then
dbms_output.put_line(‘File does not exist!’);
else
v_flag := dbms_lob.fileisopen(v_file);
if v_flag =0 then
dbms_lob.fileopen(v_file,dbms_lob.file_readonly);
end if;
v_length := dbms_lob.getlength(v_file);
dbms_lob.loadclobfromfile(v_clob, v_file, v_length, v_src_offset, v_des_offset, 0, v_lang_context, v_waring);
end if;
dbms_output.put_line(‘File length :’||dbms_lob.getlength(v_file));
dbms_output.put_line(‘Return length :’||dbms_lob.getlength(v_clob));
dbms_lob.fileclose(v_file);
return v_clob;
end;
/

–test
select p_get_elob(‘T’,’t.txt’)||’@’ from dual;

P_GET_ELOB(‘T’,’T.TXT’)||’@’
——————————————————————————–
Data Pump default directory object created:
directory object name: DATA_PUMP_DIR
creation date: 11-FEB-2009 01:52
END!
@

File length :121
Return length :121

References:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_lob.htm about operating lob
http://www.mydatabasesupport.com/forums/oracle-server/244585-tuning-clob-usage.html about lob performance
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_1.shtml about genernal lob idea
metalink doc 61737.1

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

6 Responses to How to operate lob by using DBMS_LOB in 11g

  1. alexzeng says:

    –Another example of operating external lob, same result as p_get_elob in the post
    create or replace function p_get_elob2(p_directory varchar2, p_filename varchar2)
    return clob
    as
    v_file bfile;
    v_flag integer :=0;
    v_length number :=0;
    v_pos number :=1;
    v_amount number :=32767;
    v_raw raw(32767);
    v_buf varchar2(32767);
    v_clob clob;
    v_first integer :=1;
    begin
    dbms_lob.createtemporary(v_clob, true, dbms_lob.session);
    v_file := bfilename(p_directory,p_filename);
    v_flag := dbms_lob.fileexists(v_file);
    if v_flag =0 then
    dbms_output.put_line(‘File does not exist!’);
    else
    v_flag := dbms_lob.fileisopen(v_file);
    if v_flag =0 then
    dbms_lob.fileopen(v_file,dbms_lob.file_readonly);
    end if;
    v_length := dbms_lob.getlength(v_file);
    while v_length>0 loop
    if v_length select p_get_elob2(‘T’,’t.txt’)||’@’ from dual;

    P_GET_ELOB2(‘T’,’T.TXT’)||’@’
    ——————————————————————————–
    Data Pump default directory object created:
    directory object name: DATA_PUMP_DIR
    creation date: 11-FEB-2009 01:52
    END!
    @

    File length :121
    Return length :121

  2. Wu Qihua says:

    Good examples. Does it work against securefile in 11g, the new version of LOB?

  3. alexzeng says:

    I didn’t test it yet, you may try and let us know, 😉
    Thanks your comment, qihua!
    Regards,
    Alex

  4. alexzeng says:

    I got to know from oracle 11g new feature triaining that the data layer interface for SecureFiles is the same as with BasicFiles. That is to say, it works for SecureFiles lob as well.
    Regards,
    Alex

  5. neworacledba says:

    This is a great feature in oracle 11g

  6. siva says:

    Hello,
    A column contains encrypted large data,i need to read it & replace a string in that data using lob replace.can anybody help on this issue.

    Thanks in advance,

    with regard’s
    siva

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: