plsql 插入blob

plsql 插入blob

SQL> create bigfile tablespace t_xifenfei datafile 'e:/oradata/orcl/t_blob.dbf' size 128M autoextend on;

表空间已创建。


SQL> create table u0425.t_blob(a int, b varchar(10), c blob) tablespace t_xifenfei ;

表已创建。


SQL> create or replace directory expdp_dir as 'e:/';

目录已创建。


SQL> declare
  2  b_file bfile;
  3  b_lob blob;
  4  begin
  5  insert into u0425.t_blob(a,b,c) values(1,'xifenfei', empty_blob()) return c into b_lob;
  6  b_file:=bfilename('EXPDP_DIR','0430.DMP');
  7  dbms_lob.open(b_file,dbms_lob.file_readonly);
  8  dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
  9  dbms_lob.close(b_file);
 10  commit;
 11  end;
 12  /

PL/SQL 过程已成功完成。

SQL> select * from u0425.t_blob where rownum<2;

         A B
---------- ----------
C
--------------------------------------------------------------------------------
         1 xifenfei
0301914E3B98006780BC00019FC70146A1A3633DBC4B089DCF3DFCD951D559000000010000100003
6907E6041E0D3037000000020200000077D10000000000FA1A880022535953222E225359535F4558


SQL> select a,b,dbms_lob.getlength(c) from u0425.t_blob where rownum<2;

         A B          DBMS_LOB.GETLENGTH(C)
---------- ---------- ---------------------
         1 xifenfei               142024704

插入的blob字段和实际文件大小，内容一致,插入是成功的