oracle DBMS_LOB.SUBSTR() 抛出“字符串缓冲区太小”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13819551/
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
DBMS_LOB.SUBSTR() throwing "character string buffer too small" error
提问by Jyotirup
Does oracle have a method to get substring based on number of bytes from a CLOB field?
oracle 是否有根据 CLOB 字段的字节数获取子字符串的方法？
select DBMS_LOB.SUBSTR(a.COMMENTS, 3998, 1)
FROM FOO;
I am getting error:
我收到错误：
"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
“ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小”
. 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.
. 问题出在特殊字符上。每个新的特殊字符需要 8 个字节，所以当我将字符串限制减少到 3992 时，它就可以工作了。
DBMS_LOB.SUBSTR(a.COMMENTS, 3992, 1) works.
For testing purpose I put many special characters and again it throws same error.
出于测试目的，我放置了许多特殊字符，但它再次抛出相同的错误。
Does oracle have any method which finds substring based on number of bytes than number of characters?
oracle 是否有任何方法可以根据字节数而不是字符数查找子字符串？
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?
实际上，我们正在从表中获取数据，并且需要显示在 UI 上，限制为 4000 个字符。所以，我们只想获取前 4000 个字符。由于，一个字符大小为 1 个字节，我们可以容纳 4000 个字节。因此，如果我们使用DBMS_LOB.CONVERTTOBLOB，我们可能无法正确显示获取的字符串。我们可以以某种方式将其转换回字符字符串吗？
回答by Hongyu Zhang
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)
我成功地使用了旧的 SUBSTR 函数，它也适用于 clob 类型。在这种情况下，它是 SUBSTR(a.COMMENTS, 1, 3992)
回答by Nitish0402
First, try to replace all special characters from the Character string and then try to substring it to convert it to Character.
首先，尝试替换 Character 字符串中的所有特殊字符，然后尝试将其子串化以将其转换为 Character。
Example:- DBMS_LOB.SUBSTR(REGEXP_REPLACE(your_column, '[^0-9A-Za-z]', ''),3999,1)
示例：- DBMS_LOB.SUBSTR(REGEXP_REPLACE(your_column, '[^0-9A-Za-z]', ''),3999,1)
回答by Tomas Simcik
It's an old problem, but I did not find any solution to it, so I will post my approach to find solution for this problem, just in case someone needs to handle this...
这是一个老问题，但我没有找到任何解决方案，所以我会发布我的方法来寻找这个问题的解决方案，以防万一有人需要处理这个......
My task was to retrieve "the most" characters from CLOB..
我的任务是从 CLOB 中检索“最多”字符。
DBMS_LOB.SUBSTR nor SUBSTR will achieve the correct result...
DBMS_LOB.SUBSTR 或 SUBSTR 将获得正确的结果...
In case of using DBMS_LOB.SUBSTR I keep getting ORA-12801 with ORA-06502 Using SUBSTR another ORA-64203..
在使用 DBMS_LOB.SUBSTR 的情况下，我不断收到 ORA-12801 和 ORA-06502 使用 SUBSTR 另一个 ORA-64203 ..
Suggestions say to do SUBSTR for max 1000, because maximum number of bytes for character is 4, therefore You won't get more then 4000 bytes, unfortunately that's not good enough for me, because You might receive only 2000 bytes this way, in case that certain row does not contain multi-byte characters...
建议说最大 1000 做 SUBSTR，因为字符的最大字节数是 4，因此你不会得到超过 4000 字节，不幸的是这对我来说还不够好，因为你可能只收到 2000 字节这种方式，以防万一某行不包含多字节字符...
My solution, which shouldn't be used for repeated queries by the way (rather for extracting and loading/storing data into VARCHAR2 column), is:
我的解决方案不应该用于重复查询（而不是用于提取和加载/存储数据到 VARCHAR2 列），是：
create or replace FUNCTION SUBSTR_MULTIBYTE_CLOB
(
P_DATA IN CLOB
, P_START_INDEX IN NUMBER
) RETURN VARCHAR2 AS
P_OUT VARCHAR2(4000 BYTE);
P_LENGTH NUMBER := 4000;
BEGIN
FOR loop_counter IN 1..400 LOOP
BEGIN
P_OUT := DBMS_LOB.SUBSTR(P_DATA,P_LENGTH-((loop_counter-1)*10),P_START_INDEX);
RETURN P_OUT;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -12801 OR SQLCODE = -6502 OR SQLCODE = -1401 OR SQLCODE = -1489 THEN
NULL; -- suppresses ORA-12801 "error signal from parallel server" or ORA-06502 exception "character string buffer too small" and some others I've got...
ELSE
RAISE;
END IF;
END;
END LOOP;
END SUBSTR_MULTIBYTE_CLOB;
If needed, You can change loop to 4000 and decrease it by one byte, I've decided to go by 10, just to make it little bit faster...
如果需要，您可以将循环更改为 4000 并将其减少一个字节，我决定减少 10，只是为了让它更快一点......
回答by Ajith Sasidharan
try this method::
试试这个方法::
Use Oracle function LENGTHB()to get this result. there is a way around convert CLOBto BLOBby using DBMS_LOB.CONVERTTOBLOBand use DBMS_LOB.GET_LENGTH(). this is will return no of bytes.
使用 Oracle 函数LENGTHB()获得此结果。有一种方法可以通过使用DBMS_LOB.CONVERTTOBLOB并使用DBMS_LOB.GET_LENGTH()来将CLOB转换为BLOB。这将不会返回字节数。
You can use this thread for complete answer :: https://forums.oracle.com/forums/thread.jspa?threadID=2133623
您可以使用此线程获取完整答案 :: https://forums.oracle.com/forums/thread.jspa?threadID=2133623
