保存数据库中的图像文件(BLOB)到磁盘,存储格式无所谓,最好还是图像 怎么样写一段存储过程,将存储在本地数据库中的图像文件(blob,>32K)保存到本地的某一个磁盘里,比如E盘,文件有几兆大小,存储格式无所谓,最好还是图像,困惑中,急需帮助,谢谢。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 帮你帖一段吧,很好用:1.确认现有对象SQL> col fdesc for a30SQL> select fid,fname,fdesc from eygle_blob; FID FNAME FDESC---------- -------------------------------------------------- ------------------------------ 1 ShaoLin.jpg 少林寺-康熙手书 2 DaoYing.jpg 倒映2.创建存储DirectorySQL> connect / as sysdbaConnected.SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';Directory created.SQL>SQL> grant read,write on directory BLOBDIR to eygle;Grant succeeded.SQL>3.创建存储过程SQL> connect eygle/eygleConnected.SQL>SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varchar2,poname varchar2) IS 2 l_file UTL_FILE.FILE_TYPE; 3 l_buffer RAW(32767); 4 l_amount BINARY_INTEGER := 32767; 5 l_pos INTEGER := 1; 6 l_blob BLOB; 7 l_blob_len INTEGER; 8 BEGIN 9 SELECT FPIC 10 INTO l_blob 11 FROM eygle_blob 12 WHERE FNAME = piname; 13 14 l_blob_len := DBMS_LOB.GETLENGTH(l_blob); 15 l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767); 16 17 WHILE l_pos < l_blob_len LOOP 18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer); 19 UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE); 20 l_pos := l_pos + l_amount; 21 END LOOP; 22 23 UTL_FILE.FCLOSE(l_file); 24 25 EXCEPTION 26 WHEN OTHERS THEN 27 IF UTL_FILE.IS_OPEN(l_file) THEN 28 UTL_FILE.FCLOSE(l_file); 29 END IF; 30 RAISE; 31 END; 32 /Procedure created.4.取出数据SQL> host ls -l d:\oradata\Pictotal 7618-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpgSQL> exec eygle_dump_blob('ShaoLin.jpg','01.jpg')PL/SQL procedure successfully completed.SQL> host ls -l d:\oradata\Pictotal 11072-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpgSQL>SQL> exec eygle_dump_blob('DaoYing.jpg','02.jpg')PL/SQL procedure successfully completed.SQL> host ls -l d:\oradata\Pictotal 15236-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg-rwxrwxrwa 1 Administrators SYSTEM 2131553 Apr 26 07:19 02.jpg-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg 15 l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767); 16 17 WHILE l_pos < l_blob_len LOOP 18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer); 19 UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);这个我用过了,文件>32k的时候是会出错的,ORA-29285 wb模式在我这里报错ORA-29281: 模式无效,十分郁闷,其他都一样,您贴的这些东西我原来就是这么写的,呵呵。就是不知道咋搞得PUT_RAW到一定程度的时候就异常了 是啊,我也是改成w模式的但是没用,写到一定程度就不行了,就报“文件写入错误了”错误时候,pos正好是32000,也就是再写就超过32767了 不行啊,我之前就是w模式打开的,但是写到一定程度就不行了,pos正好是32000再写就超过32767了 ORA-01722: invalid number 关于Default,Keep,Recycle三个buffer的问题 在oracle数据库中,如何建立两帐数据表的主外键关系? to_char的用法 急救!! SQL语句慢的问题 初学oracle,关于sequence的一点疑问 如何用like实现搜索英文单词 条件查询的SELECT语句执行后返回结果,可是相同功能的代参数的存储过程就提示未选定行 请教,sco unix下Oracle用哪个版本?? delphi&oracle的select语句问题! 如何把blob字段中的jpg格式转换成bmp 存储过程 根据一个表对另一个表进行更新
1.确认现有对象
SQL> col fdesc for a30
SQL> select fid,fname,fdesc from eygle_blob; FID FNAME FDESC
---------- -------------------------------------------------- ------------------------------
1 ShaoLin.jpg 少林寺-康熙手书
2 DaoYing.jpg 倒映2.创建存储Directory
SQL> connect / as sysdba
Connected.
SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';Directory created.SQL>
SQL> grant read,write on directory BLOBDIR to eygle;Grant succeeded.SQL>3.创建存储过程
SQL> connect eygle/eygle
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varchar2,poname varchar2) IS
2 l_file UTL_FILE.FILE_TYPE;
3 l_buffer RAW(32767);
4 l_amount BINARY_INTEGER := 32767;
5 l_pos INTEGER := 1;
6 l_blob BLOB;
7 l_blob_len INTEGER;
8 BEGIN
9 SELECT FPIC
10 INTO l_blob
11 FROM eygle_blob
12 WHERE FNAME = piname;
13
14 l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
15 l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767);
16
17 WHILE l_pos < l_blob_len LOOP
18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
19 UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
20 l_pos := l_pos + l_amount;
21 END LOOP;
22
23 UTL_FILE.FCLOSE(l_file);
24
25 EXCEPTION
26 WHEN OTHERS THEN
27 IF UTL_FILE.IS_OPEN(l_file) THEN
28 UTL_FILE.FCLOSE(l_file);
29 END IF;
30 RAISE;
31 END;
32 /Procedure created.4.取出数据
SQL> host ls -l d:\oradata\Pic
total 7618
-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpgSQL> exec eygle_dump_blob('ShaoLin.jpg','01.jpg')PL/SQL procedure successfully completed.SQL> host ls -l d:\oradata\Pic
total 11072
-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg
-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpgSQL>
SQL> exec eygle_dump_blob('DaoYing.jpg','02.jpg')PL/SQL procedure successfully completed.SQL> host ls -l d:\oradata\Pic
total 15236
-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg
-rwxrwxrwa 1 Administrators SYSTEM 2131553 Apr 26 07:19 02.jpg
-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg
16
17 WHILE l_pos < l_blob_len LOOP
18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
19 UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
这个我用过了,文件>32k的时候是会出错的,ORA-29285