Appending and Writing to LOBs : Clob « Data Type « Oracle PL / SQL






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

1.clob data type
2.Use clob type in PL/SQL
3.Use dbms_lob.erase to remove value from clob type value
4.Use dbms_lob.fileopen to open bfile
5.Creating an Internal LOB Table
6.Copying Internal LOBs
7.DBMS_LOB package.
8.Print out the length of the CLOBs previously INSERTed
9.Compare clob data
10.Compare two clobs
11.Append data to clob
12.Assign string value to clob type variable
13.Clob type column
14.Copy clob data
15.In() function with clob data
16.Initialize clob
17.Read clob type data, DBMS_LOB.READ
18.Use between ... and with clob data
19.Use clob to store xml data
20.clob type data as string
21.erase clob data
22.use like operator with clob type data