日期:2014-05-17  浏览次数:20910 次

如何把大于32767的CLOB或者BLOB写入到文件中
如题。求达人

------解决方案--------------------
收藏好久了,给你

SQL code
CREATE TABLE image(NAME VARCHAR2(100),DATA BLOB); 
DECLARE 
  LOB_LOC  BLOB; 
  SRC_FILE BFILE; 
  DST_FILE BLOB; 
  LGH_FILE BINARY_INTEGER; 
BEGIN 
  INSERT INTO IMAGE 
  VALUES 
    ('my_1.png', EMPTY_BLOB()) 
  RETURNING DATA INTO LOB_LOC; 

  SRC_FILE := BFILENAME('DDD', 'my.png'); 

  SELECT DATA INTO DST_FILE FROM IMAGE WHERE NAME = 'my_1.png' FOR UPDATE; 

  DBMS_LOB.FILEOPEN(SRC_FILE, DBMS_LOB.FILE_READONLY); 
  LGH_FILE := DBMS_LOB.GETLENGTH(SRC_FILE); 
  DBMS_LOB.LOADFROMFILE(DST_FILE, SRC_FILE, LGH_FILE); 

  UPDATE IMAGE SET DATA = DST_FILE WHERE NAME = 'my_1.png'; 

  DBMS_LOB.FILECLOSE(SRC_FILE); 
  COMMIT; 
END;