Search notes:

Oracle: DBMS_LOB

dbms_lob accesses LOB data via LOB locators.

Functions and procedures

All functions and procedures in the dbms_lob package have a parameter that takes a LOB locator.
LOB locators that are created by either empty_blob() or empty_clob() cannot be used for these parameters.
append Append one lob to another. Compare with writeappend.
clob2file Write a clob to a bfile.
close Closes an internal or external LOB.
compare Compares entire or partial LOBs.
converttoblob Convert a clob or an nclob to blob, using the given character set.
converttoclob Opposite of converttoblob. Compare with to_clob()
copy Copies portions from one LOB to a range of another LOB.
copy_dbfs_link
copy_from_dbfs_link
createtemporary
dbfs_link_generate_path
erase Erases an entire or a part of an internal LOB
fileclose Closes a bfile.
filecloseall
fileexists Checks if the given bfile locator points to an existing file on the server's file system.
filegetname
fileisopen
fileopen Opens a BFILE for read-only access (The parameter open_mode must be equal to the default value file_readonly, otherwise an error is thrwon)
fragment_delete
fragment_insert
fragment_move
fragment_replace
freetemporary
getchunksize
getcontenttype
getlength Returns the length of a blob in bytes or of a clob in characters.
getoptions Determine a LOB's compression, deduplication and encryption settings.
get_dbfs_link
get_dbfs_link_state
get_deduplicate_regions
get_storage_limit
instr
isopen
isremote
issecurefile
istemporary
loadblobfromfile
loadclobfromfile
loadfromfile
move_to_dbfs_link
open
read
setcontenttype
setoptions
set_dbfs_link
substr For a clob, substr returns a substring as varchar2, with a given amount of characters, starting at a given position (offset). For a blob, it returns the bytes indicated bytes as a raw.
trim Decreases the length of the lob to a given length in bytes or characters. Do not confuse with the functionality of the SQL function trim which removes white spaces from a varchar2.
write
writeappend Compare with append and write

createTemporary

Compare with empty_blob() and empty_clob()
See also createtemporary and freetemporary.

clob_from_varchars

set verify off
set define off
set feedback off 

create table tq84_varchar2 (
  i number primary key,
  v varchar2(400)
);

insert into tq84_varchar2 values (1, 'foo');
insert into tq84_varchar2 values (2, 'bar baz');
insert into tq84_varchar2 values (3, 'more foo');
insert into tq84_varchar2 values (4, 'and so on');

commit;

create table tq84_table_with_clob (
  i number primary key,
  c clob
);

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

declare
  c_ clob;
begin

  select c into c_ from tq84_table_with_clob where i = 1 for update;

  for r in (select v from tq84_varchar2 order by i) loop
    dbms_lob.append(c_, r.v);
  end loop;

end;
/

commit;

select * from tq84_table_with_clob;


drop table tq84_varchar2;
drop table tq84_table_with_clob;
Github repository Oracle-Patterns, path: /Installed/dbms/lob/clob_from_varchars.sql

insert_into_with_blob

create table tq84_blob_1 (
   id number,
   bl blob
);

create table tq84_blob_2 (
   id number,
   bl blob
);


declare

  b  blob;

begin

  insert into tq84_blob_1 values (1, empty_blob()) returning bl into b;

  for i in 1 .. 1000 loop
      dbms_lob.append (b, utl_raw.cast_to_raw(rpad ('-', 1000, '-')));
  end loop;

  update tq84_blob_1 set bl = b where id = 1;

end;
/

insert into tq84_blob_2 select * from tq84_blob_1;

-- select * from tq84_blob_2;

declare

  b  blob;

begin

  select bl into b from tq84_blob_2 where id = 1;

  dbms_output.put_line('The length of the blob is: ' || to_char(dbms_lob.getLength(b), '999G999G999'));

end;
/


drop table tq84_blob_1 purge;
drop table tq84_blob_2 purge;


Github repository Oracle-Patterns, path: /Installed/dbms/lob/insert_into_with_blob.sql

max_length

create table tq84_max_length_clob (
  i number,
  c clob
);

declare
  vc varchar2(32767);

  cl clob;
begin
  
  vc := lpad('x', 32767, 'x');

  insert into tq84_max_length_clob values (1, vc);
  insert into tq84_max_length_clob values (2, empty_clob()) returning c into cl;

  for i in 1 .. 1000 loop

      dbms_lob.append(cl, lpad('y', 100, 'y'));

  end loop;

end;
/


select i, length(c) from tq84_max_length_clob;

set long 100000

select c from tq84_max_length_clob where i = 1;

select c from tq84_max_length_clob where i = 2;

drop table tq84_max_length_clob;
Github repository Oracle-Patterns, path: /Installed/dbms/lob/max_length.sql

really_long_clob

-- See clob_from_varchar2.sql

set verify   off
set define   off
set feedback off


create table tq84_varchar2 (
   i number primary key,
   v varchar2(100)
);

begin

  for i in 1 .. 100000 loop

      insert into tq84_varchar2 values (i, dbms_random.string('a', 100));

  end loop;

end;
/

create table tq84_clob (
  i number primary key,
  c clob
);


declare
  c_ clob;
begin
  insert into tq84_clob values (1, empty_clob()) returning c into c_;

  for r in (select v from tq84_varchar2 order by i) loop

      dbms_lob.append(c_, r.v);

  end loop;

end;
/
commit;

--drop table tq84_varchar2;
Github repository Oracle-Patterns, path: /Installed/dbms/lob/really_long_clob.sql

clob_from_long_string

declare
  
  c_10000   clob;
  c_20000   clob;
  c_30000   clob;
  c_40000   clob;
  c_100000  clob;

  procedure p(expected number, c clob) is
  begin

      dbms_output.put_line(expected || ' / ' || length(c));

  end p;

begin

  c_10000 :=  
   ' 0001                                                                                               ' || 
   ' 0002                                                                                               ' || 
   ' 0003                                                                                               ' || 
   ' 0004                                                                                               ' || 
   ' 0005                                                                                               ' || 
   ' 0006                                                                                               ' || 
   ' 0007                                                                                               ' || 
   ' 0008                                                                                               ' || 
   ' 0009                                                                                               ' || 
   ' 0010                                                                                               ' || 
   ' 0011                                                                                               ' || 
   ' 0012                                                                                               ' || 
   ' 0013                                                                                               ' || 
   ' 0014                                                                                               ' || 
   ' 0015                                                                                               ' || 
   ' 0016                                                                                               ' || 
   ' 0017                                                                                               ' || 
   ' 0018                                                                                               ' || 
   ' 0019                                                                                               ' || 
   ' 0020                                                                                               ' || 
   ' 0021                                                                                               ' || 
   ' 0022                                                                                               ' || 
   ' 0023                                                                                               ' || 
   ' 0024                                                                                               ' || 
   ' 0025                                                                                               ' || 
   ' 0026                                                                                               ' || 
   ' 0027                                                                                               ' || 
   ' 0028                                                                                               ' || 
   ' 0029                                                                                               ' || 
   ' 0030                                                                                               ' || 
   ' 0031                                                                                               ' || 
   ' 0032                                                                                               ' || 
   ' 0033                                                                                               ' || 
   ' 0034                                                                                               ' || 
   ' 0035                                                                                               ' || 
   ' 0036                                                                                               ' || 
   ' 0037                                                                                               ' || 
   ' 0038                                                                                               ' || 
   ' 0039                                                                                               ' || 
   ' 0040                                                                                               ' || 
   ' 0041                                                                                               ' || 
   ' 0042                                                                                               ' || 
   ' 0043                                                                                               ' || 
   ' 0044                                                                                               ' || 
   ' 0045                                                                                               ' || 
   ' 0046                                                                                               ' || 
   ' 0047                                                                                               ' || 
   ' 0048                                                                                               ' || 
   ' 0049                                                                                               ' || 
   ' 0050                                                                                               ' || 
   ' 0051                                                                                               ' || 
   ' 0052                                                                                               ' || 
   ' 0053                                                                                               ' || 
   ' 0054                                                                                               ' || 
   ' 0055                                                                                               ' || 
   ' 0056                                                                                               ' || 
   ' 0057                                                                                               ' || 
   ' 0058                                                                                               ' || 
   ' 0059                                                                                               ' || 
   ' 0060                                                                                               ' || 
   ' 0061                                                                                               ' || 
   ' 0062                                                                                               ' || 
   ' 0063                                                                                               ' || 
   ' 0064                                                                                               ' || 
   ' 0065                                                                                               ' || 
   ' 0066                                                                                               ' || 
   ' 0067                                                                                               ' || 
   ' 0068                                                                                               ' || 
   ' 0069                                                                                               ' || 
   ' 0070                                                                                               ' || 
   ' 0071                                                                                               ' || 
   ' 0072                                                                                               ' || 
   ' 0073                                                                                               ' || 
   ' 0074                                                                                               ' || 
   ' 0075                                                                                               ' || 
   ' 0076                                                                                               ' || 
   ' 0077                                                                                               ' || 
   ' 0078                                                                                               ' || 
   ' 0079                                                                                               ' || 
   ' 0080                                                                                               ' || 
   ' 0081                                                                                               ' || 
   ' 0082                                                                                               ' || 
   ' 0083                                                                                               ' || 
   ' 0084                                                                                               ' || 
   ' 0085                                                                                               ' || 
   ' 0086                                                                                               ' || 
   ' 0087                                                                                               ' || 
   ' 0088                                                                                               ' || 
   ' 0089                                                                                               ' || 
   ' 0090                                                                                               ' || 
   ' 0091                                                                                               ' || 
   ' 0092                                                                                               ' || 
   ' 0093                                                                                               ' || 
   ' 0094                                                                                               ' || 
   ' 0095                                                                                               ' || 
   ' 0096                                                                                               ' || 
   ' 0097                                                                                               ' || 
   ' 0098                                                                                               ' || 
   ' 0099                                                                                               ' || 
   ' 0100                                                                                               ';

   p(10000, c_10000);

   c_20000 := c_10000 || c_10000;
   p(20000, c_20000);

   c_30000  := c_20000 || c_10000;
   p(30000, c_30000);

   c_40000  := c_20000 || c_20000;
   p(40000, c_40000);

   c_100000 := c_40000 || c_40000 || c_20000;
   p(100000, c_100000);

end;
/

Github repository Oracle-Patterns, path: /Installed/dbms/lob/clob_from_long_string.sql

See also

Oracle DBMS PL/SQL Packages, dbms_lobutil.
ORA-22288: file or LOB operation … failed

Index