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
– Alex Poole, Commented Jul 30, 2015 at 10:15
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
– Alex Poole, Commented Jul 30, 2015 at 10:16
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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 30, 2015 at 11:22
Possible duplicate of Getting an Error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" — J. Chomel
– J. Chomel, Commented Apr 26, 2016 at 12:01

Bar · Accepted Answer · 2018-12-30 08:59:51Z

0

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.

answered Dec 30, 2018 at 8:59

Bar

14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related