CLOBs are very useful structures. You can store lots of text information in a CLOB.
SQL>
SQL> create table catalog
2 (id number,
3 name VARCHAR2(2000),
4 manual_cl CLOB,
5 firstpage_bl BLOB,
6 mastertxt_bf BFILE
7 );
Table created.
SQL>
SQL> create directory IO as 'C:\IO';
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_file_bf BFILE;
3 v_manual_cl CLOB;
4 lang_ctx NUMBER := DBMS_LOB.default_lang_ctx;
5 charset_id NUMBER := 0;
6 src_offset NUMBER := 1;
7 dst_offset NUMBER := 1;
8 warning NUMBER;
9 begin
10 update catalog set manual_cl = EMPTY_CLOB() where id = 1;
11
12 select mastertxt_bf, manual_cl into v_file_bf, v_manual_cl from catalog where id = 1;
13
14 DBMS_LOB.fileopen(v_file_bf, DBMS_LOB.file_readonly);
15 DBMS_LOB.loadclobfromfile (v_manual_cl, v_file_bf,DBMS_LOB.getlength (v_file_bf),
16 src_offset, dst_offset,charset_id, lang_ctx,warning);
17 DBMS_LOB.fileclose (v_file_bf);
18 end;
19 /
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 14
SQL>
SQL> drop directory IO;
Directory dropped.
SQL>
SQL> drop table catalog;
Table dropped.