ANBOB™

SQL> conn system/oracle
Connected.
SQL> create directory dir_base as '/oracle';

Directory created.

SQL> grant read,write on directory dir_base to anbob;

Grant succeeded.

SQL> conn anbob/anbob;
Connected.
SQL> host
[oracle@aix ~]$ echo i am anbob >>/oracle/note.txt
[oracle@aix ~]$ cat /oracle/note.txt
i am anbob
[oracle@aix ~]$ exit
exit

SQL> create table testblob(id int,comm blob);

Table created.

SQL> insert into testblob values(1,to_blob('1'));

1 row created.
SQL> insert into testblob values(1,to_blob('a'));

1 row created.

SQL> insert into testblob values(1,to_blob('1a'));

1 row created.

SQL> select dbms_lob.substr(comm,1,1) from testblob;

DBMS_LOB.SUBSTR(COMM,1,1)
---------------------------------------------
01
0A
1A

SQL> declare
  2  tb blob;
  3  bfile_name bfile:=bfilename('DIR_BASE','note.txt');
  4  begin
  5  insert into testblob values(3,empty_blob())
  6  returning comm into tb;
  7  dbms_lob.fileopen(bfile_name);
  8  dbms_lob.loadfromfile(tb,bfile_name,dbms_lob.getlength(bfile_name));
  9  dbms_lob.fileclose(bfile_name);
 10  commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.substr(comm,10,1) from testblob;

DBMS_LOB.SUBSTR(COMM,10,1)
---------------------------------------------------------------------
01
0A
1A
6920616D20616E626F62

SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(comm,10,1)) from testblob;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(COMM,10,1))
--------------------------------------------------------------------




i am anbob


[oracle@aix ~]$ exp anbob/anbob tables=testblob file=blob

Export: Release 10.2.0.4.0 - Production on Thu Aug 11 14:59:53 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                       TESTBLOB          4 rows exported

note:
16进制可以用to_blob直接insert,dbms_lob.substr取出,参数顺序略不同于没包的substr，bfile的插入稍有复杂，blob类型可以exp直接导出

打赏

微信扫一扫，打赏作者吧～