如何避免“原始变量长度太长"? SQL Developer中的错误?

问题描述

我正在用大量文本更新BLOB，但出现此错误:

I am updating a BLOB with large mount of text and I get this error:

SQL Error: ORA-06502: PL/SQL: numeric or value error: raw variable length too long

有什么办法解决吗?

如，并且查询中的所有内容都位于一行中.

The text is 2,670 characters long, being converted via utl_i18n.string_to_raw, as explained in How do I edit BLOBs (containing JSON) in Oracle SQL Developer?, and is all on one line in the query.

更新:有问题的BLOB已经包含2686个字符长的文本，比我要插入的文本还要长.

Update: The BLOB in question already contains text that is 2,686 characters long, which is longer than the text I am trying to insert.

推荐答案

RAW限制为2000个字节.如果您的数据长于此长度，则需要将其存储在CLOB中，然后将CLOB转换为BLOB，不幸的是，该功能比string_to_raw函数要复杂一些.假设您可以将整个字符串分配给CLOB变量，只要该字符串的长度小于32676字节，该变量就可以正常工作.如果它长于此长度，则需要分段写入CLOB，然后转换为BLOB.

A RAW is limited to 2000 bytes. If your data is longer than that, you'll need to store it in a CLOB and then convert the CLOB to a BLOB which is, unfortunately, a bit more complicated that the string_to_raw function. Something like this will work assuming you can assign the entire string to a CLOB variable which should work as long as the string is less than 32676 bytes in length. If it's longer than that, you'll need to write to the CLOB in pieces and then convert to a BLOB.

declare
  l_blob        blob;
  l_clob        clob := rpad('{"foo": {"id": "1", "value": "2", "name": "bob"}}',3200,'*');
  l_amt         integer := dbms_lob.lobmaxsize;
  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
  l_csid        integer := dbms_lob.default_csid;
  l_ctx         integer := dbms_lob.default_lang_ctx;
  l_warn        integer;
begin
  dbms_lob.createTemporary( l_blob, false );
  dbms_lob.convertToBlob( l_blob,
                          l_clob,
                          l_amt,
                          l_dest_offset,
                          l_src_offset,
                          l_csid,
                          l_ctx,
                          l_warn );
  update json_data
     set data = l_blob;
end;
/

这篇关于如何避免“原始变量长度太长"? SQL Developer中的错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！