更新BLOB列ORA-01461:只能绑定一个长值以插入到一个长列中

Question

我想将一个过程存储在BLOB列中，不幸的是，我无法向您展示代码，但它包含126行代码。这是我正在使用的更新。

DECLARE   str varchar2(20000);
BEGIN   str :='create or replace A_LONG_PROCEDURE  procedure .........';
UPDATE tab1 SET  report=str
WHERE reference= 'TER';
commit;
end;
/

我得到了错误ORA-01461; can bind a LONG value only for insert into a LONG column

我试过用更新。set ='CREATE OR REPLACE LONG_PROCEDURE ...'我得到错误01704字符串小到长

如何将长过程更新为blob列？

Answer 1

这应该是可行的，考虑将列改为阻塞。

DECLARE   
  declare str tab1.report%type;
BEGIN   
  str:= to_blob(utl_raw.cast_to_raw('create or replace     A_LONG_PROCEDURE...'));

  UPDATE tab1 SET  report=str
  WHERE reference= 'TER';

  commit;
end;
/

Answer 2

您需要将列类型更改为CLOB或将CLOB变量初始化为过程文本，然后在插入之前使用dbms_lob.ConvertToBlob过程将其转换为BLOB。

Oracle 11g的示例：

表设置：

create table lob_sample(
  id         number,
  clob_field clob,
  blob_field blob
);

insert into lob_sample(id) values(1);

代码：

declare 
  vSomeLongString varchar2(32767);
  vClob CLOB;          
  vBlob BLOB;
begin

  -- Build sample string from all_objects view
  for cFill in (
    select object_type||' '||owner||'.'||object_name||'; '||chr(10) sample_string
    from all_objects
  ) loop                                              

    vSomeLongString := vSomeLongString || cFill.sample_string;
    if(length(vSomeLongString) > 32000) then exit; end if;
  end loop;                                       


  -- String may be directly assigned to CLOB
  vClob := vSomeLongString;

  -- And it's possible to simple assign CLOB variable value to CLOB field 
  update lob_sample set clob_field = vClob where id = 1;

  -- Even, string value may be directly assigned to CLOB field
  update lob_sample set clob_field = vSomeLongString where id = 1;

  -- But for BLOB field conversion are needed.     
  -- BLOB is just a set of raw bytes and it may present 
  -- any character set as well as non-text data.
  dbms_lob.CreateTemporary(vBlob, false);                       
  declare
    vDestOffset  integer := 1;
    vSrcOffset   integer := 1;
    vDefaultCSID number  := dbms_lob.default_csid;
    vLangContext integer := dbms_lob.default_lang_ctx;
    vWarning     integer;
  begin               
    dbms_lob.convertToBlob(
      vBlob, vClob, dbms_lob.LobMaxSize,
      vDestOffset, vSrcOffset, vDefaultCSID, vLangContext, vWarning
    );  
  end;  

  update lob_sample set blob_field = vBlob where id = 1;

  dbms_lob.freetemporary(vBlob);

end;