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;