sql - 如何将 Oracle 列中的 Blob 写入文件系统

Question

my_images 表由一个名为 images 的 blob 列组成。我需要将这些图像写入我的 image_dir，即“C:\TEMP”。

执行以下PL/SQL代码时，只有第一张图片作为图片写入目录。第二个blob写成0字节(空)，没有其他(应该是总数8)。

所以循环似乎没有正常工作。我正在使用 Oracle 11g 快捷版 (XE) 和 SQL Developer。这是错误和代码:

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 代码

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;

Answer 1

主要问题与NOT相关重新初始化参数v_offset为1(如声明部分):

v_offset := 1;

for every image id just before

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

assignment.

此外，问题可能出现在尚未关闭或已打开的 blob 上。为了防止这些，

replace

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;

和

replace

dbms_lob.close(i.v_blob);

with

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