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> 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> 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