Writes into a CLOB : CLOB « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. CLOB

SQL> CREATE TABLE facebook (
  2     name VARCHAR2(80),
  3     photo BLOB,
  4     directions CLOB,
  5     description NCLOB,
  6     web_page BFILE);

Table created.

SQL>
SQL> CREATE DIRECTORY bfile_data AS 'c:\xxx';

Directory created.

SQL>
SQL> DECLARE
  2     directions CLOB;
  3     amount BINARY_INTEGER;
  4     offset INTEGER;
  5     first_direction VARCHAR2(100);
  6     more_directions VARCHAR2(500);
  7  BEGIN
  8
  9     DELETE FROM facebook WHERE name='Falls';
 10
 11     INSERT INTO facebook(name,directions)VALUES ('Falls',EMPTY_CLOB());
 12
 13     SELECT directions INTO directions FROM facebook WHERE name='Falls';
 14
 15     DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
 16
 17     first_direction := 'across the Bridge.';
 18     amount := LENGTH(first_direction);
 19     offset := 1;
 20     DBMS_LOB.WRITE(directions, amount, offset, first_direction);
 21
 22     more_directions := ' right.';
 23     DBMS_LOB.WRITEAPPEND(directions,LENGTH(more_directions), more_directions);
 24
 25     more_directions := ' more.';
 26     DBMS_LOB.WRITEAPPEND(directions,LENGTH(more_directions), more_directions);
 27
 28     DBMS_LOB.CLOSE(directions);
 29  END;
 30  /

PL/SQL procedure successfully completed.

SQL>
SQL> SET LONG 2000
SQL> COLUMN directions WORD_WRAPPED FORMAT A70
SQL>
SQL> SELECT directions FROM facebook WHERE name='Falls';

DIRECTIONS
----------------------------------------------------------------------
across the Bridge. right. more.

SQL>
SQL>
SQL>
SQL> drop table facebook;

Table dropped.

SQL> drop directory bfile_data;

Directory dropped.

SQL>