DBMS_LOB.SUBSTR() throwing "character string buffer too small" error

Question

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;

I am getting error:

"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"

. 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.

Does oracle have any method which finds substring based on number of bytes than number of characters?

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?

Answer 1

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)

Answer 2

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..

DBMS_LOB.SUBSTR nor SUBSTR will achieve the correct result...

In case of using DBMS_LOB.SUBSTR I keep getting ORA-12801 with ORA-06502 Using SUBSTR another 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...

My solution, which shouldn't be used for repeated queries by the way (rather for extracting and loading/storing data into VARCHAR2 column), is:

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...