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.
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_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;