DBMS_LOB.SUBSTR()抛出“字符字符串缓冲区太小".错误 [英] DBMS_LOB.SUBSTR() throwing "character string buffer too small" error
问题描述
oracle是否有一种基于CLOB字段中的字节数获取子字符串的方法?
Does oracle have a method to get substring based on number of bytes from a CLOB field?
select DBMS_LOB.SUBSTR(a.COMMENTS, 3998, 1)
FROM FOO;
我遇到错误:
"ORA-06502:PL/SQL:数字或值错误:字符串缓冲区 太小了
"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
. 问题出在特殊字符上.每个新的特殊字符都占用8个字节,因此当我将字符串限制减少到3992时,它就可以工作.
. The problem was in special characters. Each new special character takes 8 bytes so when I reduce the string limit to 3992 then it works.
DBMS_LOB.SUBSTR(a.COMMENTS, 3992, 1) works.
出于测试目的,我放置了许多特殊字符,并再次抛出相同的错误.
For testing purpose I put many special characters and again it throws same error.
oracle有什么方法可以根据字节数而不是字符数来查找子字符串吗?
Does oracle have any method which finds substring based on number of bytes than number of characters?
实际上,我们正在从表中获取数据,并且需要以4000个字符的限制在UI上显示.因此,我们只想获取前4000个字符.因为一个字符的大小是1个字节,所以我们可以容纳4000个字节.因此,如果使用DBMS_LOB.CONVERTTOBLOB
,则可能无法正确显示提取的字符串.我们可以以某种方式将其转换回字符字符串吗?
Actually, we are fetching data from a table and need to display on UI with a limitation of 4000 characters. So, we want to fetch first 4000 characters only. As, a character size is 1 byte, we can accomodate 4000 bytes. So, if we use DBMS_LOB.CONVERTTOBLOB
, we may not be able to display properly the characters string fetched. Can we convert it back it charater string somehow?
推荐答案
我成功使用了旧的SUBSTR函数,该函数也适用于clob类型.在这种情况下,它是SUBSTR(a.COMMENTS,1,3992)
I used the old SUBSTR function successfully, which works for the clob type as well. In this case, it's SUBSTR(a.COMMENTS, 1, 3992)
这篇关于DBMS_LOB.SUBSTR()抛出“字符字符串缓冲区太小".错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!