如何避免 SQL Developer 中的“原始变量长度太长”错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9743084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I avoid "raw variable length too long" errors in SQL Developer?
提问by Xonatron
I am updating a BLOB with large mount of text and I get this error:
我正在更新带有大量文本的 BLOB，但出现此错误：
SQL Error: ORA-06502: PL/SQL: numeric or value error: raw variable length too long
Is there any way around it?
有什么办法可以解决吗？
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.
文本长度为 2,670 个字符，通过 转换utl_i18n.string_to_raw，如如何在 Oracle SQL Developer 中编辑 BLOB（包含 JSON）中所述？, 并且都在查询中的一行上。
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.
更新：有问题的 BLOB 已经包含 2,686 个字符长的文本，这比我尝试插入的文本长。
采纳答案by Justin Cave
A RAWis limited to 2000 bytes. If your data is longer than that, you'll need to store it in a CLOBand then convert the CLOBto a BLOBwhich is, unfortunately, a bit more complicated that the string_to_rawfunction. Something like this will work assuming you can assign the entire string to a CLOBvariable 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 CLOBin pieces and then convert to a BLOB.
ARAW限制为 2000 字节。如果您的数据比这更长，则需要将其存储在 a 中CLOB，然后将CLOBa转换为 a BLOB，不幸的是，这比string_to_raw函数要复杂一些。假设您可以将整个字符串分配给一个CLOB变量，只要字符串的长度小于 32676 字节，该变量就可以工作，这样的事情将起作用。如果它比这更长，则需要写入CLOBin 部分，然后转换为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;
/
回答by Sachin Kainth
I guess what the issue is that the data that you are inserting to the column is too big for the datatype of the column. Please check the value you are trying to insert into the column and then update the column datatype.
我想问题是您插入到列中的数据对于列的数据类型来说太大了。请检查您尝试插入列中的值，然后更新列数据类型。
