首页 » ORACLE » BLOB select、insert、exp with sqlplus

BLOB select、insert、exp with sqlplus

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直接导出

打赏

目前这篇文章有2条评论(Rss)评论关闭。

  1. Santa Wims | #1
    2011-12-21 at 14:57

    Much of this is a Great blog, but sometimes I can by my side with me on his nice apartment and found the same book. I would be very happy to make your stay in my holiday home and see you soon for me.

  2. 丰禾棋牌 | #2
    2011-08-16 at 09:43

    技术贴 顶一个 看不懂