如何使用DBMS_LOB从文件中加载CLOB数据

SQL> create table t (
  2  name       varchar2(30),
  3  content clob
  4  )
  5  /
Table created.
SQL> 
SQL> 
SQL> create or replace directory dir1 as '/opt/oracle';
Directory created.
SQL> 
SQL> declare
  2          l_bfile bfile;
  3          l_clob  clob;
  4          l_str   varchar2(1000);
  5  begin
  6          insert into t (name,content)
  7          values ('b.txt',empty_clob())
  8          returning content into l_clob;
  9  
 10          l_bfile := bfilename('DIR1','b.txt');
 11  
 12          dbms_lob.fileopen(l_bfile);
 13          dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
 14          dbms_lob.fileclose(l_bfile);
 15          commit;
 16          exception when others then
 17          l_str:=sqlerrm(sqlcode);
 18          dbms_output.put_line(l_str);
 19  end;
 20  /
PL/SQL procedure successfully completed.
SQL> select * from t;  
NAME                           CONTENT
------------------------------ ----------------------------------------------
b.txt                          ？test
                               Oracle BFile数据加载问题
                               研究
SQL> 
SQL> declare
  2          l_bfile bfile;
  3          l_clob  clob;
  4          l_str   varchar2(1000);
  5  begin
  6          insert into t (name,content)
  7          values ('b.txt',empty_clob())
  8          returning content into l_clob;
  9  
 10          l_bfile := bfilename('DIR1','b.txt');
 11  
 12          dbms_lob.fileopen(l_bfile);
 13          dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile)-2,
 14                            src_offset => 3);
 15          dbms_lob.fileclose(l_bfile);
 16          commit;
 17          exception when others then
 18          l_str:=sqlerrm(sqlcode);
 19          dbms_output.put_line(l_str);
 20  end;
 21  /
PL/SQL procedure successfully completed.
SQL> select * from t;
NAME                           CONTENT
------------------------------ --------------------------------------
b.txt                          ？test测试
                               Oracle BFile数据加载问题
                               研究
b.txt                          test测试
                               Oracle BFile数据加载问题
                               研究
            

--------------------next---------------------