Hi Teresa
I have just changed my example, just to make it with more data – I can then use the example generally.
If you copy-and-paste the code – then remember to change TABLESPACE in the create table and the user must have DBA rights to change tablespace to read only and back.
I do not use function or other stuff – I only show the fun stuff about LOB and data.
column text format a50;
-- Change or create tablespace TABLE_DATA, LOB_SPACE and INDEX_DATA
create table aol( aolID integer primary key,
Text CLOB
) tablespace table_data
lob (text) store as (tablespace lob_space disable storage in row
storage( initial 128k next 128k pctincrease 0)
chunk 2048 pctversion 20 nocache nologging
index (tablespace index_data storage( initial 128k next 128k ) )
);
-- CHUNK must be a multiplex of database block size (=DB block size).
declare
a clob;
b clob;
d clob;
begin
insert into aol values(1, Rpad('aaa', 4000 )); -- This is A lob
insert into aol values(2, Rpad('aba', 4000 )); -- This is B lob
insert into aol values(3, Rpad('aca', 4000 )); -- This is C lob
insert into aol values(4, NULL ); -- This is D lob
-- Before we can work with the LOB we need to lock to row
select text into a from aol where aolid = 1 for update;
-- Append LOB to the LOB
dbms_lob.append(a, a);
-- Update LOB - we still lock the row until commit or rollback
update aol
set text = a
where aolid = 1;
-- append 4000 char to the end of LOB
dbms_lob.writeappend(a, 4000, rpad('ffffffffffffffffff',4000) );
-- Write 4000 char starting at pos 200
dbms_lob.write(a, 4000, 200, rpad('ffffffffffffffffff',4000) );
-- Now we copy the LOB to another lob - B LOB
-- We need to lock the B lob and get the LOB locator
select text into b from aol where aolid = 2 for update;
-- now lets copy A lob to B lob - we want all of A lob
dbms_lob.copy( B, A, dbms_lob.getlength(a) );
-- Update LOB - we still lock the row until commit or rollback
update aol
set text = b
where aolid = 2;
-- We can make a copy just by saying LOB D = LOB A (if same LOB type)
-- we do copy the data - not just change the locator to the same as A
update aol
set text = a
where aolid = 4;
-- save works and release LOCKS
commit;
end;
/
-- Lets see the data from pos 1 and 30 in length
select aolid, dbms_lob.SubStr(text, 30, 1 ) as text, dbms_lob.getlength( text) len from aol;
/*
PL/SQL-procedure er udført.
AOLID TEXT LEN
---------- ------------------------------ ----------
1 aaa 12000
2 aaa 12000
3 aca 4000
4 aaa 12000
*/
-- Now we change data on D lob to see what A and D lob are different
declare
d clob;
begin
-- Remember to lock the row
select text into d from aol where aolid = 4 for update;
-- Write 4000 char starting at pos 1
dbms_lob.write(d, 4000, 1, rpad('DDDDDDDDDDDDDDDDDDD',4000) );
-- Update LOB - we still lock the row until commit or rollback
update aol
set text = d
where aolid = 4;
-- save works and release LOCKS
commit;
end;
/
-- Lets see the data from pos 1 and 30 in length
select aolid, dbms_lob.SubStr(text, 30, 1 ) as text, dbms_lob.getlength( text) len from aol;
/*
PL/SQL-procedure er udført.
AOLID TEXT LEN
---------- ------------------------------ ----------
1 aaa 12000
2 aaa 12000
3 aca 4000
4 DDDDDDDDDDDDDDDDDDD 12000
*/
-- Lets see some data at pos 200 and 30 char in length
select aolid, dbms_lob.SubStr(text, 30, 200 ) as text, dbms_lob.getlength( text) len from aol;
/*
AOLID TEXT LEN
---------- ------------------------------ ----------
1 ffffffffffffffffff 12000
2 ffffffffffffffffff 12000
3 4000
4 12000
*/
-- let me prove it by setting the LOB tablespace as READONLY
-- We get an error because we do not only update locator value but create the LOB data as a copy
alter tablespace lob_space read only;
insert into aol values(5, (select text from aol where aolid = 4) );
/* *
ERROR in line 1:
ORA-00372: file 20 cannot be modified at this time
ORA-01110: Datafile 20: 'F:\ORACLE\ORADATA\SQL\LOB_SPACE01.DBF'
*/
-- So even if we only think that we change locator value - Oracle makes the magic for us
-- We then always have to think of locator as the pointer to the real data and the real
-- data must exists in a file and not just in memory
-- Cleaning
alter tablespace lob_space read write;
drop table aol;
Regards
Allan
Icq: 346225948
