请问Oracle中如何读取lob类型 在Oracle中如何读取lob类型的数据。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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.jpg SQL> 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.jpg SQL> 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 网上找到的,这是连接http://oracle.chinaitlab.com/PLSQL/36942.html 简化一下楼上的,呵呵。一、select 语句取得 lob 指针: 9 SELECT FPIC 10 INTO l_blob 11 FROM eygle_blob 12 WHERE FNAME = piname; 二、dbms_lob 包中的函数读取 lob 字段的内容: 14 l_blob_len := DBMS_LOB.GETLENGTH(l_blob); 18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer); 江湖救急啊 ORA-16032 文件传输监控,即判断指定目录文件是否存在 Oracle有没有提供对SQL语句预处理的接口? 怎样查询某个字段不含指定字符的记录 为什么总弹出对话框:java.exe 遇到问题需要关闭。我们对此引起的不便表示抱歉。 一个表空间管理的问题,散分! oracle数据库的插入操作是立即执行吗? 在艰难中得以毕业,同时为了感谢论坛上的朋友的帮助,散分,请朋友们接受我的好意吧-------- 请告诉我Oracle数据如何导出,导入,和备份!有高分赠! oracle 只是比较分钟问题?? 一个表空间导入数据的问题
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.jpg
SQL> 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.jpg
SQL>
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
9 SELECT FPIC
10 INTO l_blob
11 FROM eygle_blob
12 WHERE FNAME = piname; 二、dbms_lob 包中的函数读取 lob 字段的内容:
14 l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);