BLOB字段操作

置为空或NULL
update blob_test set b_content = null;
update blob_test set b_content = EMPTY_BLOB();

判断内容不为空
select * from blob_test where dbms_lob.getlength(b_content) > 0;  
select * from blob_test where dbms_lob.getchunksize(b_content) > 0;

判断为null
select * from blob_test where b_content is null;

判断为空
select * from blob_test where dbms_lob.getlength(b_content) = 0;

根据目前项目的特点，有一个第三方厂家写入的一个表，记录图片信息，原来使用线程导出后，清空blob字段，现在完全改写为数据库自身实现。这样更高效
例子：之后再用job调用存储过程
CREATE OR REPLACE PROCEDURE GET_PIC_BLOB(pk_id number) IS
  l_file         UTL_FILE.FILE_TYPE;
  l_buffer       RAW(32767);
  l_amount       BINARY_INTEGER := 32767;
  l_pos          INTEGER := 1;
  in_front_blob  BLOB;
  in_back_blob   BLOB;
  out_front_blob BLOB;
  out_back_blob  BLOB;
  l_blob_len     INTEGER;

BEGIN
  select t.in_front_pic, t.in_back_pic into in_front_blob,in_back_blob
    from car_pic t
   where (dbms_lob.getlength(in_front_pic) > 0 and
         dbms_lob.getlength(in_back_pic) > 0) and
          id = pk_id;
   begin
  l_blob_len := DBMS_LOB.GETLENGTH(in_front_blob);
  l_file     := UTL_FILE.FOPEN('TEST1',
                               pk_id || '_in_front' || '.jpg',
                               'WB',
                               32767);
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ(in_front_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_FILE.FCLOSE(l_file);

  l_pos :=1;
EXCEPTION

  --WHEN NO_DATA_FOUND THEN
  --DBMS_OUTPUT.put_line('no data : ' || i_xh);
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(l_file) THEN
      UTL_FILE.FCLOSE(l_file);
      RAISE;
    END IF;
    end;
   
       begin
  l_blob_len := DBMS_LOB.GETLENGTH(in_back_blob);
  l_file     := UTL_FILE.FOPEN('TEST1',
                               pk_id || '_in_back' || '.jpg',
                               'WB',
                               32767);
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ(in_back_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_FILE.FCLOSE(l_file);
  l_pos := 1;

EXCEPTION

  --WHEN NO_DATA_FOUND THEN
  --DBMS_OUTPUT.put_line('no data : ' || i_xh);
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(l_file) THEN
      UTL_FILE.FCLOSE(l_file);
      RAISE;
    END IF;
    end;
   
END GET_PIC_BLOB;