请教：如何写存入blob类型的sql语句？急用！谢谢！

水中玉

请教：我想用blob类型存入一个word文档，在sql plus中如何写该语句？谢谢！请帮忙写出该语句！！！（假设现在word文档存在d盘）急用！！！

ximcn

create table demo
( id int primary key,
theBlob blob
)
/

create or replace directory my_files as '/export/home/tkyte/public_html';

declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;

l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
dbms_lob.fileopen( l_bfile );

dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );

dbms_lob.fileclose( l_bfile );
end;
/

水中玉

上面的程序在sqlplus中运行不了啊，declear和begin...end也不是sqlplus语句啊，哪维高手能否提供一个全的sql语句啊，谢谢！！

pydwh

[quote]原帖由 "水中玉"]上面的程序在sqlplus中运行不了啊，declear和begin...end也不是sqlplus语句啊，哪维高手能否提供一个全的sql语句啊，谢谢！！[/quote 发表：

可以的啊。。。。   

1. 1、在sysdba下建立目录别名
   
2. SQL>; conn sys/myoracle as sysdba
   
3. 已连接。
   
4. SQL>; create or replace directory my_files as 'e:\photo';
   
5. 
   
6. 目录已创建。
   
7. //授权给用户scott
   
8. SQL>; grant read on directory my_files to scott;
   
9. 
   
10. 授权成功。
    
11. 2、建立一个含有blob类型的字段的表
    
12. SQL>; show user;
    
13. USER 为"SCOTT"
    
14. SQL>; create table demo
    
15.   2  ( id int primary key,
    
16.   3  theBlob blob
    
17.   4  )
    
18.   5  /
    
19. 
    
20. 表已创建。
    
21. 3、插入
    
22. SQL>; declare
    
23.   2  l_blob blob;
    
24.   3  l_bfile bfile;
    
25.   4  begin
    
26.   5  insert into demo values ( 1, empty_blob() )
    
27.   6  returning theBlob into l_blob;
    
28.   7
    
29.   8  l_bfile := bfilename( 'MY_FILES', 'girl.jpg' );
    
30.   9  dbms_lob.fileopen( l_bfile );
    
31. 10
    
32. 11  dbms_lob.loadfromfile( l_blob, l_bfile,
    
33. 12  dbms_lob.getlength( l_bfile ) );
    
34. 13
    
35. 14  dbms_lob.fileclose( l_bfile );
    
36. 15  end;
    
37. 16  /
    
38. 
    
39. PL/SQL 过程已成功完成。

复制代码

pydwh

另外，下面的链接文档中有很详细的说明。。。
http://www.chinaunix.net/cgi-bin/bbs/topic.cgi?forum=8&topic=2732

水中玉

我用上面的方法的时候，在声明的时候出现下面的错误，我把我的过程拷过来请帮我解决一下，谢谢！！
SQL>;  create or replace directory a as 'd:\oracle' ;

目录已创建。

SQL>; grant read on directory a to scott;

授权成功。

SQL>; show user;
USER 为"SYS"
SQL>;  declare
  2    l_blob blob;
  3   l_bfile bfile;
  4   begin
  5   insert into system.demo values( 1, empty_blob() )
  6   returning theBlob into l_blob;
  7   l_bfile := bfilename( 'a','A');
  8  sys.dbms_lob.fileopen( l_bfile );
  9  sys.dbms_lob.loadfromfile( l_blob, l_bfile,
10  sys. dbms_lob.getlength( l_bfile ) );
11  sys.dbms_lob.fileclose( l_bfile );
12   end;
13  /
declare
*
ERROR 位于第 1 行:
ORA-22285: 对不存在的目录或文件进行FILEOPEN操作
ORA-06512: 在"SYS.DBMS_LOB", line 475
ORA-06512: 在line 8

我应该怎么解决呢？A文件是一个图片，就在我指定的目录下，请帮忙！！谢谢！

水中玉

我用上面的方法的时候，在声明的时候出现下面的错误，我把我的过程拷过来请帮我解决一下，谢谢！！
SQL>;  create or replace directory a as 'd:\oracle' ;

目录已创建。

SQL>; grant read on directory a to scott;

授权成功。

SQL>; show user;
USER 为"SYS"
SQL>;  declare
  2    l_blob blob;
  3   l_bfile bfile;
  4   begin
  5   insert into system.demo values( 1, empty_blob() )
  6   returning theBlob into l_blob;
  7   l_bfile := bfilename( 'a','A');
  8  sys.dbms_lob.fileopen( l_bfile );
  9  sys.dbms_lob.loadfromfile( l_blob, l_bfile,
10  sys. dbms_lob.getlength( l_bfile ) );
11  sys.dbms_lob.fileclose( l_bfile );
12   end;
13  /
declare
*
ERROR 位于第 1 行:
ORA-22285: 对不存在的目录或文件进行FILEOPEN操作
ORA-06512: 在"SYS.DBMS_LOB", line 475
ORA-06512: 在line 8

我应该怎么解决呢？A文件是一个图片，就在我指定的目录下，请帮忙！！谢谢！

pydwh

你把目录大写试一下...
呵呵,oracle挺有趣的,比如你通过表名查询时条件中如果表名不是大写,查不到结果的...
SQL>; show user
USER 为"SCOTT"
SQL>; create or replace directory myphoto as 'e:\photo';

目录已创建。

//目录小写的情况
SQL>; declare
  2  l_blob blob;
  3  l_bfile bfile;
  4  begin
  5  insert into demo values ( 2, empty_blob() )
  6  returning theBlob into l_blob;
  7
  8  l_bfile:= bfilename('myphoto', 'girl.jpg' );
  9  dbms_lob.fileopen(l_bfile);
10
11  dbms_lob.loadfromfile( l_blob, l_bfile,
12  dbms_lob.getlength(l_bfile));
13
14  dbms_lob.fileclose(l_bfile);
15  end;
16  /
declare
*
ERROR 位于第 1 行:
ORA-22285: 对不存在的目录或文件进行FILEOPEN操作
ORA-06512: 在"SYS.DBMS_LOB", line 504
ORA-06512: 在line 9

//目录大写的情况
SQL>; declare
  2  l_blob blob;
  3  l_bfile bfile;
  4  begin
  5  insert into demo values ( 2, empty_blob() )
  6  returning theBlob into l_blob;
  7
  8  l_bfile:= bfilename('MYPHOTO', 'girl.jpg' );
  9  dbms_lob.fileopen(l_bfile);
10
11  dbms_lob.loadfromfile( l_blob, l_bfile,
12  dbms_lob.getlength(l_bfile));
13
14  dbms_lob.fileclose(l_bfile);
15  end;
16  /

PL/SQL 过程已成功完成。

//检查结果,我那张图片(girl.jpg)是159 KB (163,568 字节)的,通过检查插入后的长度,可知,正确保存在数据库中了.
SQL>; select dbms_lob.getlength(THEBLOB) from demo where id=2;

DBMS_LOB.GETLENGTH(THEBLOB)
---------------------------
                     163568

水中玉

万分感谢，我已经成功的存入了， 谢谢！
版主不愧是版主，厉害！！！！
呵呵！如果修改或者删除blob类型数据的语句应该怎么写呢，谢谢！！
致敬！！

pydwh

呵呵,我只是菜鸟来的...

删除很简单,直接删除行就行了。
SQL>; delete from demo where id=1;

已删除 1 行。

修改就要看要求了，如果是图片之类的，实质上是只能替换。
如果是文本，sql仅可以处理整个lob，不能操作lob的数据片，故只能追加，这个可以参改这个文档http://www.syerp.com/oracle/2002005.php。
现在都以替换Blob字段的操作为例(其实我认为基本上修改还是用替换比较合适)，方法是先清空Blob字段的内容，然后再执行装载即可。
//先插入图片girl1.jpg

1. declare
   
2. l_blob blob;
   
3. l_bfile bfile;
   
4. begin
   
5. insert into demo values ( 2, empty_blob() )
   
6. returning theBlob into l_blob;  
   
7. l_bfile:= bfilename('MYPHOTO', 'girl1.jpg' );
   
8. dbms_lob.fileopen(l_bfile);
   
9. dbms_lob.loadfromfile( l_blob, l_bfile,
   
10. dbms_lob.getlength(l_bfile));  
    
11. dbms_lob.fileclose(l_bfile);
    
12. end;
    
13. /
    
14. PL/SQL 过程已成功完成。
    
15. SQL>; select dbms_lob.getlength(THEBLOB) from demo where id=2;
    
16. 
    
17. DBMS_LOB.GETLENGTH(THEBLOB)
    
18. ---------------------------
    
19.                       59547

复制代码

//修改为图片girl.jpg

1. SQL>; declare
   
2.   2  l_blob blob;
   
3.   3  l_bfile bfile;
   
4.   4  begin
   
5.   5
   
6.   6  update demo set theBlob=empty_blob() where id=2
   
7.   7  returning theBlob into l_blob;
   
8.   8  l_bfile := bfilename( 'MYPHOTO', 'girl.jpg' );
   
9.   9  dbms_lob.fileopen( l_bfile );
   
10. 10
    
11. 11  dbms_lob.loadfromfile( l_blob, l_bfile,
    
12. 12  dbms_lob.getlength( l_bfile ) );
    
13. 13
    
14. 14  dbms_lob.fileclose( l_bfile );
    
15. 15  end;
    
16. 16  /
    
17. 
    
18. PL/SQL 过程已成功完成。
    
19. SQL>; select dbms_lob.getlength(THEBLOB) from demo where id=2;
    
20. 
    
21. DBMS_LOB.GETLENGTH(THEBLOB)
    
22. ---------------------------
    
23.                      163568

复制代码