Oracle lob加载bfile数据到blob字段中

DBMS_LOB.LOADBLOBFROMFILE(从BFILE加载内容到BLOB)
[oracle@blliu ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sun May 23 16:01:58 2010

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> host;
[oracle@blliu ~]$ pwd
/home/oracle
[oracle@blliu ~]$ ls bg.jpg
bg.jpg
[oracle@blliu ~]$ exit
exit

SQL> create directory test_dir as '/home/oracle';

Directory created.

SQL> create table test (id number,img bfile);

Table created.

SQL> insert into test values (111,bfilename('TEST_DIR','bg.jpg'));

1 row created.

SQL> commit;

Commit complete.

SQL> create table test2 (id number ,img blob);

Table created.

SQL> insert into test2 values (111,empty_blob());

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2  dl test2.img%type;
  3  sb test.img%type;
  4  do number:= 1;
  5  so number:= 1;
  6  begin
  7  select img into dl from test2 where id=111 for update;
  8  select img into sb from test where id=111;
  9  dbms_lob.fileopen(
 10  file_loc=>sb,
 11  open_mode=>dbms_lob.FILE_READONLY);
 12  dbms_lob.loadblobfromfile(
 13  dest_lob=>dl,
 14  src_bfile=>sb,
 15  amount=>dbms_lob.lobmaxsize,
 16  dest_offset=>do,
 17  src_offset=>so
 18  );
 19  dbms_lob.close(file_loc=>sb);
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(img) from test;

DBMS_LOB.GETLENGTH(IMG)
-----------------------
                   6618

SQL> select dbms_lob.getlength(img) from test2;

DBMS_LOB.GETLENGTH(IMG)
-----------------------
                   6618

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-663457/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-663457/