blob/clob类型数据的存取方法

CREATE OR REPLACE PROCEDURE TEST_BLOB IS

  lv_photo    BLOB;
  lv_file     BFILE;
  lv_length   INT;
  src_offset  INT := 1;
  dest_offset INT := 1;
  lv_buffer   RAW(2000);
  lv_newfile  utl_file.file_type;

BEGIN
  -- write photo
  DELETE FROM fac.test_blob;
  COMMIT;
  INSERT INTO fac.test_blob VALUES ('AAA', empty_blob());

  COMMIT;

  --lock record
  SELECT photo INTO lv_photo FROM fac.test_blob FOR UPDATE;

  --get file
  lv_file := bfilename('FAC_INBOUND_CC', 'Blue hills.jpg');

  --open file
  dbms_lob.fileopen(lv_file, 0);

  --get file length
  lv_length := dbms_lob.getlength(lv_file);

  --write file to table
  dbms_lob.loadblobfromfile(lv_photo,
                            lv_file,
                            lv_length,
                            dest_offset,
                            src_offset);

  dbms_lob.fileclose(lv_file);

  COMMIT;

  --read photo

  --get field
  SELECT photo INTO lv_photo FROM fac.test_blob;

  --read 2000 byte in every loop
  lv_length := 2000;

  -- create new file
  -- utl_file.fremove('FAC_OUTBOUND', 'TEST_BLOB1.jpg');

  lv_newfile := utl_file.fopen('FAC_OUTBOUND',
                               'TEST_BLOB' ||
                               TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || '.jpg',
                               'W',
                               32767);

  LOOP
 
    BEGIN
      -- read data
      dbms_lob.READ(lv_photo, lv_length, src_offset, lv_buffer);
   
      --write to file
      utl_file.put_raw(lv_newfile, lv_buffer);
   
      --start position
      src_offset := src_offset + 2000;
    EXCEPTION
      WHEN no_data_found THEN
        EXIT;
    END;
 
  END LOOP;

  utl_file.fclose(lv_newfile);

END;

 

因为每次读取只能2000 byte, 因此需要用循环来取。然后用exception来判断是否到文件尾