Loading data to the CLOB by using BFILE : CLOB « Large Objects « Oracle PL/SQL Tutorial

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

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