ORA-01403:未找到数据ORA-06512:在"SYS.DBMS_LOB"，第972行ORA-06512:第54行

Question

我做了一个匿名过程从BLOB字段(图像)下载图像，但我得到了一个错误，它在第57行触发: DBMS_LOB.READ(lob_loc => v_lob_loc，amount => v_amount，offset => v_offset，buffer => v_buffer)；

 DECLARE
    cursor c is 
    select id,REG_CODE  from STUDENT where DBMS_LOB.GETLENGTH(IMAGE)>0;
          v_lob_loc      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 c
loop

 SELECT  IMAGE
INTO    v_lob_loc
FROM   STUDENT
WHERE   id =i.id;

-- +-------------------------------------------------------------+
-- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
-- +-------------------------------------------------------------+
v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

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(v_lob_loc, DBMS_LOB.LOB_READONLY);

-- +-------------------------------------------------------------+
-- | WRITE CONTENTS OF THE LOB TO A FILE                         |
-- +-------------------------------------------------------------+
v_out_file := UTL_FILE.FOPEN(
    location      => 'ALL_IMG_DIR', 
    filename      => I.REG_CODE||'.JPG', 
    open_mode     => 'wb',
    max_linesize  => 32767);

WHILE v_amount >= v_buffer_size
LOOP

 DBMS_LOB.READ(           lob_loc    => v_lob_loc,           amount     => v_amount,          offset     => v_offset,          buffer     => 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);


END LOOP;

UTL_FILE.FFLUSH(file => v_out_file);

UTL_FILE.FCLOSE(v_out_file);

-- +-------------------------------------------------------------+
-- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
-- +-------------------------------------------------------------+
DBMS_LOB.CLOSE(v_lob_loc);

UPDATE STUDENT SET IMAGE =EMPTY_BLOB() WHERE ID=I.ID;

end loop;


END;

Answer 1

这个指向oracle文档的链接解释了如何处理这种类型的异常-您可以在标题“BFILE特定的规则和限制”下找到它，它们解释了这种异常来自于到达文件的末尾，以及如何将您的循环包装在一个异常处理程序中以捕获此错误并正确地处理它。