Obtain Clob data pointer : CLOB « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. CLOB

SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);

Table created.

SQL>
SQL> INSERT INTO myClob VALUES (101,null);

1 row created.

SQL>
SQL> declare
  2    clob_pointer CLOB;
  3    v_buf VARCHAR2(1000);
  4    Amount BINARY_INTEGER :=1000;
  5    Position INTEGER :=1;
  6  BEGIN
  7    v_buf :=rpad('A',1000,'A');
  8
  9    insert into myClob values (5,EMPTY_CLOB());
 10
 11    SELECT clob_data INTO clob_pointer FROM myClob WHERE id =5 FOR UPDATE;
 12
 13    DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
 14
 15    FOR i IN 1..500 LOOP
 16
 17      DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
 18
 19      Position :=Position +Amount;
 20
 21    END LOOP;
 22
 23    DBMS_LOB.CLOSE (clob_pointer);
 24  END;
 25  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myClob;

Table dropped.