用存储过程取数据库中BLOB并把它保存到文件中,

[hjd1399]

declare
lobloc blob;
count int;
offset int:=1;
buffer raw(1000);
handle utl_file.file_type;
begin
select pdf into lobloc from test where id='0001';
count := dbms_lob.getlength(lobloc);
dbms_lob.read(lobloc,coun,offset,buffer);
handle:= utl_file.fopen('INFO','a.pdf','w',1000);
utl_file.put_raw(handle,buffer);
utl_file.fclose(handle);
end;
写到文件里以后就变成了乱码,大一点的文件写到文件里数据就是空的.
谁能告诉我怎么将BLOB列正确的下载下来.

[Eric_1999]

CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler" AS
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;

public class BlobHandler
{

public static void ExportBlob(String myFile, BLOB myBlob) throws Exception
{
// Bind the image object to the database object
// Open streams for the output file and the blob
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);
InputStream inStream = myBlob.getBinaryStream();

// Get the optimum buffer size and use this to create the read/write buffer
int size = myBlob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

// Transfer the data
while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}

// Close everything down
inStream.close();
outStream.close();
}

};
/

ALTER java source "BlobHandler" compile;
show errors java source "BlobHandler"
--=======================================
--创建BLOB保存为文件的存储过程
--=======================================
CREATE OR REPLACE PROCEDURE ExportBlob (p_file IN VARCHAR2,
p_blob IN BLOB)
AS LANGUAGE JAVA
NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';
/
-==-======================================================
--授权数据库用户可以保存文件至磁盘，应以DBA身份登录
--注：SCHEMA－NAME应填写数据库用户名，应注意大写字母
--========================================================
EXEC Dbms_Java.Grant_Permission( -
'SCHEMA-NAME', -
'java.io.FilePermission', -
'<<ALL FILES>>', -
'read ,write, execute, delete');
--=======================================
--测试
--=======================================
--创建数据库（测试）
CREATE TABLE tab1 (col1 BLOB);
INSERT INTO tab1 VALUES(empty_blob());
COMMIT;
--执行存储过程，生成磁盘文件
DECLARE
v_blob BLOB;
BEGIN
SELECT col1
INTO v_blob
FROM tab1;

ExportBlob('c:\MyBlob',v_blob);
END;
/

[hjd1399]

我是楼主


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,'w', 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 /
从库里面取出来的PDF文件却打不开.说是文件损坏,打不开.
如果是WORD文档的话却是乱码.
取出来的文件总比元文件大了几K,

希望大家能给我指点几下.

[Eric_1999]

pdf和word文件都是有自己格式的，不知道格式是不能正确打开的，你写的procedure只能打开纯文本文件。

[hjd1399]

我是楼主
PDF文件怎样读取呀,

[hjd2357]

顶

[F15Eagle]

顶