如何将Blob从Oracle列写入文件系统 [英] How to Write Blob from Oracle Column to the File System

问题描述

my_images表由称为图像的Blob列组成.我需要将这些图像写入到我的image_dir中，即"C:\ TEMP".

my_images table consists of a blob column called images. I need to write these images to my image_dir which is 'C:\TEMP'.

当执行以下PL/SQL代码时，仅第一个映像作为映像写入目录.第二个Blob被写为0字节(空)，没有其他字节(总数应为8).

When the following PL/SQL code is executed, only the first image is written to the directory as an image. The second blob is written as 0 byte (empty) and there is no other (Should be a total number of 8).

因此循环似乎无法正常工作.我正在使用Oracle 11g Express Edition(XE)和SQL Developer.这是错误和代码:

So the loop does not seem to work correctly. I am using Oracle 11g Express Edition (XE) and SQL Developer. Here is the error and the code:

Error starting at line : 53 in command -
BEGIN write_blob_to_file_v5; END;
Error report -
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_LOB", line 1056
ORA-06512: at "SYS.WRITE_BLOB_TO_FILE_V5", line 40
ORA-06512: at line 1
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

---

PL/SQL代码

---

PL/SQL code

CREATE OR REPLACE PROCEDURE write_blob_to_file_v5
AS
   v_lob_image_name   VARCHAR (100);
   v_lob_image_id     NUMBER;
   v_blob             BLOB;
   v_buffer           RAW (32767);
   v_buffer_size      BINARY_INTEGER;
   v_amount           BINARY_INTEGER;
   v_offset           NUMBER (38) := 1;
   v_chunksize        INTEGER;
   v_out_file         UTL_FILE.file_type;
BEGIN
   FOR i IN (SELECT DBMS_LOB.getlength (v_blob) v_len,
                    image_id v_lob_image_id,
                    "IMAGE_NAME" v_lob_image_name,
                    image v_blob
               FROM sys.MY_IMAGES)
   LOOP
      v_chunksize := DBMS_LOB.getchunksize (i.v_blob);

      IF (v_chunksize < 32767)
      THEN
         v_buffer_size := v_chunksize;
      ELSE
         v_buffer_size := 32767;
      END IF;

      v_amount := v_buffer_size;
      DBMS_LOB.open (i.v_blob, DBMS_LOB.lob_readonly);
      v_out_file :=
         UTL_FILE.fopen (
            location       => 'IMAGE_DIR',
            filename       => (   ''
                               || i.v_lob_image_id
                               || '_'
                               || i.v_lob_image_name
                               || '.JPG'),
            open_mode      => 'wb',
            max_linesize   => 32767);

      WHILE v_amount >= v_buffer_size
      LOOP
         DBMS_LOB.read (i.v_blob,
                        v_amount,
                        v_offset,
                        v_buffer);
         v_offset := v_offset + v_amount;
         UTL_FILE.put_raw (file        => v_out_file,
                           buffer      => v_buffer,
                           autoflush   => TRUE);
         UTL_FILE.fflush (file => v_out_file);
      --utl_file.new_line(file => v_out_file);
      END LOOP;

      UTL_FILE.fflush (v_out_file);
      UTL_FILE.fclose (v_out_file);
      DBMS_LOB.close (i.v_blob);
   END LOOP;
END;

推荐答案

主要问题与NOT到re-initialize参数v_offset到1有关(如声明部分所示):

The main problem is related NOT to re-initialize the parameter v_offset to 1 ( as in the declaration section ) :

v_offset := 1;

对于

v_chunksize := dbms_lob.getchunksize(i.v_blob); 

分配.

此外，问题可能是由于尚未关闭或已经打开的Blob而引起的.为防止这些情况，

Moreover, the problem may arise about not yet closed or already opened blobs. To prevent these,

替换

dbms_lob.open(i.v_blob,dbms_lob.lob_readonly);

with

if ( dbms_lob.isopen(i.v_blob)=0 ) then
 dbms_lob.open(i.v_blob,dbms_lob.lob_readonly); 
end if;

和

替换

dbms_lob.close(i.v_blob);

with

if ( dbms_lob.isopen(i.v_blob)=1 ) then 
 dbms_lob.close(i.v_blob); 
end if;

这篇关于如何将Blob从Oracle列写入文件系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！