Performing basic string operations on CLOBs : CLOB « Large Objects « Oracle PL/SQL Tutorial

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

SQL>
SQL> create table catalog
  2  (
  3   id number,
  4   name VARCHAR2(2000),
  5   manual_cl CLOB,
  6   firstpage_bl BLOB,
  7   mastertxt_bf BFILE
  8   );

Table created.

SQL>
SQL>
SQL> create directory IO as 'C:\IO';

Directory created.

SQL> --grant read, write on directory IO to public;
SQL>
SQL> insert into catalog(id, name, mastertxt_bf) values (1, 'TEXT.HTM', BFILENAME ('IO', 'text.htm'));

1 row created.

SQL>
SQL>
SQL> declare
  2      v_manual_cl   CLOB;
  3      v_nr          NUMBER;
  4      v_tx          VARCHAR2 (2000);
  5      v_add_tx      VARCHAR2 (2000):='Loaded: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi');
  6  begin
  7      select manual_cl into v_manual_cl from catalog where id = 1
  8      for update;
  9
 10      DBMS_LOB.writeappend (v_manual_cl,LENGTH (v_add_tx), v_add_tx);
 11
 12      v_nr := INSTR (v_manual_cl, 'Loaded:', -1);
 13      v_tx := SUBSTR (v_manual_cl, v_nr);
 14      DBMS_OUTPUT.put_line (v_tx);
 15  end;
 16
 17  drop table catalog;
 18
 19  drop directory IO;
 20
 21  --Keep in mind that LOB pointers are transaction dependent. This means that if you have a COMMIT command in your code, the LOB pointer could become invalid (not pointing to anything) and you may not be able to perform some operations by using that locator.
 22