Oracle PL/SQL - Loading data to the CLOB by using BFILE

Introduction

You can store lots of text information in a CLOB.

The following code shows how to read data from a file and place it in a CLOB column.

EMPTY_CLOB( ) is a built-in function that creates a CLOB with a length of 0 bytes.

Demo

SQL>
SQL> drop table product;
SQL> create table product(
  2     id number,
  3     name VARCHAR2(2000),--  w w  w  .j av a  2 s. c  o m
  4     manual_cl CLOB,
  5     firstpage_bl BLOB,
  6     mastertxt_bf BFILE
  7  );

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 t_product
 11       set manual_cl = EMPTY_CLOB()
 12       where id = 1;
 13
 14       select mastertxt_bf, manual_cl
 15         into v_file_bf, v_manual_cl
 16          from t_product
 17       where id = 1;
 18
 19       DBMS_LOB.fileopen(v_file_bf, DBMS_LOB.file_readonly);
 20       DBMS_LOB.loadclobfromfile (v_manual_cl,
 21                                  v_file_bf,
 22                                  DBMS_LOB.getlength (v_file_bf),
 23                                  src_offset,
 24                                  dst_offset,
 25                                  charset_id,
 26                                  lang_ctx,
 27                                  warning);
 28      DBMS_LOB.fileclose (v_file_bf);
 29  end;
 30  /

SQL>

Related Topic