SQL>
SQL> CREATE TABLE LOBS(
2 lob_index INTEGER,
3 CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,'Teach Yourself Oracle8i in 21 Days');
1 row created.
SQL> INSERT INTO LOBS VALUES(2,'Oracle Data Warehousing Unleashed');
1 row created.
SQL> INSERT INTO LOBS VALUES(3,'');
1 row created.
SQL> INSERT INTO LOBS VALUES(4,'Oracle Unleashed 2E');
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL> DECLARE
2 Source_Lob CLOB;
3 Dest_Lob CLOB;
4 Copy_Amount INTEGER;
5 BEGIN
6 SELECT CLOB_LOCATOR into Dest_LOB
7 FROM LOBS
8 WHERE LOB_INDEX = 5 FOR UPDATE; -- FOR UPDATE locks the ROW
9 SELECT CLOB_LOCATOR into Source_LOB
10 FROM LOBS
11 WHERE LOB_INDEX = 1;
12 Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
13 DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
14 COMMIT;
15 SELECT CLOB_LOCATOR into Dest_LOB
16 FROM LOBS
17 WHERE LOB_INDEX = 6 FOR UPDATE;
18 SELECT CLOB_LOCATOR into Source_LOB
19 FROM LOBS
20 WHERE LOB_INDEX = 2;
21 Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
22 DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
23 COMMIT;
24 END;
25 /
PL/SQL procedure successfully completed.
SQL> drop table lobs;
Table dropped.
SQL>
SQL> --