Appending and Writing to LOBs
SQL>
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 Write_Amount INTEGER := 10;
5 Writing_Position INTEGER ;
6 Buffer VARCHAR2(10) := 'Added Text';
7 BEGIN
8 SELECT CLOB_LOCATOR into Dest_LOB
9 FROM LOBS
10 WHERE LOB_INDEX = 5 FOR UPDATE; -- Locks Row for Update
11 SELECT CLOB_LOCATOR into Source_LOB
12 FROM LOBS
13 WHERE LOB_INDEX = 1;
14 DBMS_LOB.APPEND(Dest_LOB, Source_LOB);
15 COMMIT;
16 SELECT CLOB_LOCATOR into Source_LOB
17 FROM LOBS
18 WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update
19
20 Writing_Position := DBMS_LOB.GETLENGTH(Source_Lob) + 1;
21 DBMS_LOB.WRITE(Source_LOB,Write_Amount,Writing_Position,Buffer);
22 COMMIT;
23 END;
24 /
PL/SQL procedure successfully completed.
SQL> drop table lobs;
Table dropped.
SQL> --
Related examples in the same category