create or replace procedure export_Image(index in number,filename in varchar2) is
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
begin
SELECT JPGIMAGE
INTO l_blob
FROM Image
WHERE ID = index;l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
l_file := UTL_FILE.FOPEN('BLOBDIR',filename,'wb', l_blob_len); WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP; UTL_FILE.FCLOSE(l_file); EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
IF UTL_FILE.IS_OPEN(l_file) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
RAISE;
end export_Image;
create or replace directory BLOBDIR as 'D:\oradata\image';
host ls -l D:\oradata\image;
call export_Image(1,'1.jpg');
begin export_Image(1,'1.jpg'); end;
存储过程是在网上搜索到的,我用的本机的数据库,执行完这个存储过程后我去电脑上D:\oradata\image目录下面看
没有图片文件,请教一下大家是什么原因,我是在pl/sql 中的SQL窗口中执行的
执行过程中也没有报错,刚接触存储过程,请教大家了!!!!
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
begin
SELECT JPGIMAGE
INTO l_blob
FROM Image
WHERE ID = index;l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
l_file := UTL_FILE.FOPEN('BLOBDIR',filename,'wb', l_blob_len); WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP; UTL_FILE.FCLOSE(l_file); EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
IF UTL_FILE.IS_OPEN(l_file) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
RAISE;
end export_Image;
create or replace directory BLOBDIR as 'D:\oradata\image';
host ls -l D:\oradata\image;
call export_Image(1,'1.jpg');
begin export_Image(1,'1.jpg'); end;
存储过程是在网上搜索到的,我用的本机的数据库,执行完这个存储过程后我去电脑上D:\oradata\image目录下面看
没有图片文件,请教一下大家是什么原因,我是在pl/sql 中的SQL窗口中执行的
执行过程中也没有报错,刚接触存储过程,请教大家了!!!!
CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL);
CREATE OR REPLACE DIRECTORY IMAGES AS 'd:\temp\pic'; --IMG_INSERT:插入一张图片
CREATE OR REPLACE PROCEDURE IMG_INSERT(TID VARCHAR2, FILENAME VARCHAR2) AS
F_LOB BFILE; --文件类型
B_LOB BLOB;
BEGIN
INSERT INTO IMAGE_LOB
(T_ID, T_IMAGE)
VALUES
(TID, EMPTY_BLOB()) RETURN T_IMAGE INTO B_LOB;
--插入空的blob
F_LOB := BFILENAME('IMAGES', FILENAME);
--获取指定目录下的文件
DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);
--以只读的方式打开文件
DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB, DBMS_LOB.GETLENGTH(F_LOB));
--传递对象
DBMS_LOB.FILECLOSE(F_LOB);
--关闭原始文件
COMMIT;
END;EXEC IMG_INSERT('1','1.gif');--IMG_SAVE:写一张图片
CREATE OR REPLACE PROCEDURE IMG_SAVE(TID VARCHAR2, FILENAME VARCHAR2) IS
l_file utl_file.file_type;
l_lob BLOB;
l_offset INT := 1;
l_amount INT := 1000;
l_len INT := 0;
l_buffer RAW(1000);
BEGIN
SELECT T_IMAGE INTO l_lob FROM IMAGE_LOB WHERE T_ID = TID;
l_file := utl_file.fopen('IMAGES', FILENAME, 'wb', 1000);
l_len := dbms_lob.getlength(l_lob);
WHILE l_offset <= l_len LOOP
dbms_lob.read(l_lob, l_amount, l_offset, l_buffer);
utl_file.put_raw(l_file, l_buffer, TRUE);
l_offset := l_offset + l_amount;
END LOOP;
utl_file.fclose(l_file);
END IMG_SAVE;
EXEC IMG_SAVE('1','2.gif');
如果不存在,建立一个吧。
如果有问题,可以使用pl/sql developer单步调试一下
查看一下报什么错误,贴出错误信息。