dbms_lob package have a parameter that takes a LOB locator. 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 |
empty_blob() and empty_clob() createtemporary and freetemporary. 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;
create table tq84_clob_compare (
i number primary key,
c clob
);
insert into tq84_clob_compare values ( 1, empty_clob());
insert into tq84_clob_compare values ( 2, empty_clob());
insert into tq84_clob_compare values ( 3, empty_clob());
insert into tq84_clob_compare values ( 4, empty_clob());
insert into tq84_clob_compare values (20, empty_clob());
insert into tq84_clob_compare values (99, empty_clob());
insert into tq84_clob_compare values (99, empty_clob());
declare
c_ clob;
begin
for j in 1 .. 4 loop
select c into c_ from tq84_clob_compare where i = j;
if j = 1 then dbms_lob.append(c_, 'one' );
elsif j = 2 then dbms_lob.append(c_, 'two' );
elsif j = 3 then dbms_lob.append(c_, 'three');
elsif j = 4 then dbms_lob.append(c_, 'four' ); end if;
end loop;
select c into c_ from tq84_clob_compare where i = 20;
dbms_lob.append(c_, 'two');
end;
/
commit;
-- first one smaller than second one returns -1.
-- first one greater than second one returns 1.
-- first one equal to second one returns 0.
select t.i, u.i, dbms_lob.compare(t.c, u.c)
from tq84_clob_compare t,
tq84_clob_compare u;
drop table tq84_clob_compare;
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;
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;
-- 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;
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;
/