ORA-06502: PL/SQL: numeric or value error: character string buffer too small - Convert Clob to varchar2

Question

Im getting this error while i trying to convert clob to varchar2 using an api.

The 'body' field that im trying to retrieve is clob, However i want to convert it to varchar2(1000).

Please advice why this error occurred :

       MERGE INTO XXX D
                 USING (
                       select  
                              v_ticket_id ticket_id,
                              json_ext.get_number(json(value(S)),'id') comment_id,
                              json_ext.get_number(json(value(S)),'author_id') author_id , 
                              DBMS_LOB.SUBSTR(json_ext.get_string(json(value(S)),'body'),1000,1) body,
                              json_ext.get_string(json(value(S)),'via.channel')channel,
                              case when lower(json(value(S)).get('public').to_char()) = 'true' then 1 else 0 end  is_public,
                              to_date(replace(replace(json_ext.get_string(json(value(S)),'created_at'),'T',' '),'Z',null), 'YYYY-MM-DD HH24:MI:SS') created_at,
                              sysdate
                              FROM table(PKG.sf_retreive_data('RETREIVE').list_data) S) S

                ON (D.ticket_id = S.ticket_id and D.comment_id = S.comment_id)

                WHEN MATCHED THEN          
                      UPDATE SET   author_id = S.author_id,
                                   body = S.body,
                                   channel = S.channel,
                                   is_public = S.is_public,
                                   created_at = S.created_at, 
                                   bi_updated_time = sysdate

                WHEN NOT MATCHED THEN          
                      insert (ticket_id,comment_id,author_id ,body,channel,is_public,created_at,bi_updated_time)
                      values
                             (S.ticket_id,S.comment_id,S.author_id,S.body,S.channel,S.is_public,S.created_at,sysdate);
               commit;

And i get this error : Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

What is full exception stack? How big are the other get_string results - have you narrowed it down to the comment? — Alex Poole
Incidentally, nothing to do with the error, but you don't need your replace calls; you can embed literals in the format mask: to_date(json_ext.get_string(json(value(S)),'created_at'), 'YYYY-MM-DD"T"HH24:MI:SS"Z"'). — Alex Poole
Does the SELECT from the USING clause execute successfully if you run it by itself (i.e. not as part of the MERGE statement above)? Also, please edit your question and include the definition of the XXX table. Thanks. — Bob Jarvis - Reinstate Monica
Possible duplicate of Getting an Error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" — J. Chomel

Bar Bar · Accepted Answer · 2018-12-30T08:59:51

The problem was that the line : DBMS_LOB.SUBSTR(json_ext.get_string(json(value(S)),'body'),1000,1) body,

should has been inserted into varchar2(1000).

The function DBMS_LOB.SUBSTR wasn't working as should for this operation. And the string was bigger than the CLOB data type due to special character sets (Json obj).

The solution: insert into table(GTT) with clob column and then substr into varchar2 XXX table.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small - Convert Clob to varchar2

1 Answers