DECLARE
    dest_lob BLOB;
    src_lob  BLOB;
BEGIN
    -- get the LOB locators
    -- note that the FOR UPDATE clause locks the row
    SELECT b_lob INTO dest_lob
        FROM lob_table
        WHERE key_value = 12 FOR UPDATE;
    SELECT b_lob INTO src_lob
        FROM lob_table
        WHERE key_value =21;
        DBMS_LOB.APPEND(dest_lob, src_lob);
        COMMIT; END;  

DBMS_LOB.COMPARE (
   lob_1            IN BLOB,
   lob_2            IN BLOB,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

DECLARE
    lob_1, lob_2      BLOB;
    retval            INTEGER;
BEGIN
    SELECT b_col INTO lob_1 FROM lob_table
        WHERE key_value = 45;
    SELECT b_col INTO lob_2 FROM lob_table
        WHERE key_value = 54;
    retval := dbms_lob.compare(lob_1, lob_2, 5600, 33482,
         128);
    IF retval =0 THEN ;
        -- process  compared
    code ELSE ; -- process not
        compared code  END
    IF; END;

DECLARE
    lobd, lobs     BLOB; 
    dest_offset    INTEGER := 1
    src_offset     INTEGER := 1
    amt            INTEGER := 3000;
BEGIN
    SELECT b_col INTO lobd
        FROM lob_table
        WHERE key_value =
           
    12    FOR UPDATE; SELECT
    b_col     INTO lobs FROM
    lob_table            WHERE key_value= 

    21; DBMS_LOB.COPY(lobd, lobs, amt,
        dest_offset, src_offset);
        COMMIT; END; 

DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY BLOB,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := 10);

DECLARE
    lobd       BLOB;
    amt        INTEGER := 3000;
BEGIN
    SELECT b_col INTO lobd
        FROM lob_table
        WHERE key_value = 12 FOR UPDATE;
    dbms_lob.erase(dest_lob,       amt,
    2000);        COMMIT; END;

DBMS_LOB.INSTR (
   file_loc   IN   BFILE,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.LOADFROMFILE(
   dest_lob    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, 
   src_file    IN            BFILE, 
   amount      IN            INTEGER, 
   dest_offset IN            INTEGER := 1, 
   src_offset  IN            INTEGER := 1);

DECLARE 
  lobd       BLOB; 
  fils       BFILE   := BFILENAME('SOME_DIR_OBJ','some_file'); 
  amt        INTEGER := 4000; 
BEGIN 
  SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42 FOR 
  UPDATE;       dbms_lob.fileopen(fils, 
  dbms_lob.file_readonly);       dbms_lob.loadfromfile(lobd,   fils, amt); 
  COMMIT;        dbms_lob.fileclose(fils); END;

DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY BLOB,
   open_mode IN            BINARY_INTEGER);

DECLARE
     fil BFILE; 
     pos INTEGER;
     amt BINARY_INTEGER; 
     buf RAW(40); 
BEGIN 
     SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
     dbms_lob.open(fil, dbms_lob.lob_readonly);   
     amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; 
     dbms_lob.read(fil, amt, pos, buf); 
     dbms_output.put_line('Read
     F1 past 
     EOF: '||
     utl_raw.cast_to_varchar2(buf)); dbms_lob.close(fil); 
     exception WHEN 
no_data_found 
     THEN BEGIN dbms_output.put_line('End of File reached. Closing file');  dbms_lob.fileclose(fil);
     -- or   
     dbms_lob.filecloseall if appropriate END; END;

DECLARE
    fil BFILE;
    pos INTEGER := 2147483647;
    pattern RAW;
BEGIN
    SELECT f_lob INTO fil FROM lob_table WHERE key_value =
    21; dbms_lob.fileopen(fil,
    dbms_lob.file_readonly); 
    pattern := dbms_lob.substr(fil, 255, pos); 
    dbms_lob.fileclose(fil); END; 

DBMS_LOB.TRIM (
   lob_loc        IN OUT  NOCOPY CLOB CHARACTER SET ANY_CS,
   newlen         IN             INTEGER);

create global temporary table lob_temp
(id int primary key,
c_lob clob,
b_lob blob
)
create sequence lob_temp_seq;

create or replace
function to_blob(p_cname in varchar2,
p_tname in varchar2,
p_rowid in rowid) return blob
as
l_blob blob;
l_id int;
begin
select lob_temp_seq.nextval into l_id from dual;

 execute immediate
 'insert into lob_temp (id,b_lob)
 select :id, to_lob(' || p_cname || ')
 from ' || p_tname ||
 ' where rowid = :rid '
 using IN l_id, IN p_rowid;

 select b_lob into l_blob from lob_temp where id - l_id ;

 return l_blob;
 end;

create or replace
function to_clob(p_cname in varchar2,
p_tname in varchar2,
p_rowid in rowid) return clob
as
l_clob clob;
l_id int;
begin
select lob_temp_seq.nextval into l_id from dual;

 execute immediate
 'insert into lob_temp (id,c_lob)
 select :id, to_lob(' || p_cname || ')
 from ' || p_tname ||
 ' where rowid = :rid '
 using IN l_id, IN p_rowid;

 select c_lob into l_clob from lob_temp where id = l_id ;

 return l_clob;
end;

• Инициализация

  CREATE DIRECTORY dir_name AS 'path';
  DROP DIRECTORY dir_name;
  
  GRANT READ ON DIRECTORY dir_name TO user|role|PUBLIC;

• кладем файл в директорию
• чтение из файла в BLOB

  DECLARE
    lobd       BLOB; 
    fils       BFILE   := BFILENAME('SOME_DIR_OBJ','some_file'); 
    amt        INTEGER := 4000; 
  BEGIN 
    SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42 FOR UPDATE;
    dbms_lob.fileopen(fils, dbms_lob.file_readonly); 
    dbms_lob.loadfromfile(lobd, fils, amt); 
    COMMIT; 
    dbms_lob.fileclose(fils); 
  END;

• просмотр списка директорий