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

Toad for Oracle
1

I am facing a new challenge today with this error message. The code is below:

/* Formatted on 11/29/2016 2:32:20 PM (QP5 v5.300) */
DECLARE
CURSOR kur
IS
WITH tags_in
AS ( SELECT st.intag tag, TRIM(bcs.stragg(st.sifno)) sifno
FROM bcs.temp_sif_tags st
GROUP BY st.intag)
SELECT c.cmpnt_id, c.cmpnt_name, ti.tag, ti.sifno, LENGTH(ti.sifno) sif_len
FROM tags_in ti
LEFT JOIN component c ON REPLACE(c.cmpnt_name, ’ ') = REPLACE(ti.tag, ’ ');

v_udf_c35 VARCHAR2(20);
v_udf_c108 VARCHAR2(20);
BEGIN
pkg_bec_std.reg_session;

FOR k IN kur
LOOP
IF k.cmpnt_id IS NOT NULL THEN
v_udf_c35 := SUBSTR(k.sifno, 1, 20);
DBMS_OUTPUT.put_line('SIFNO Length = ’ || k.sif_len);

IF k.sif_len > 20 THEN
v_udf_c108 := SUBSTR(k.sifno, 21, 40);
ELSE
v_udf_c108 := NULL;
END IF;

DBMS_OUTPUT.put_line(v_udf_c35 || ’ ’ || v_udf_c108);
/*
UPDATE udf_component uc
SET uc.udf_c35 = v_udf_c35, uc.udf_c108 = v_udf_c108
WHERE uc.cmpnt_id = k.cmpnt_id;
*/
END IF;
END LOOP;
END;

I have commented out the “UPDATE” in order to debug the code, and for some reason, it is stopping whenever the value of SIF_LEN is 49. Anyone has a clue?

Thanks for your help.

2

At first sight:

v_udf_c108 VARCHAR2(20);

IF k.sif_len > 20 THEN
v_udf_c108 := SUBSTR(k.sifno, 21, 40);

Something up to size 40 won’t always fit in a varchar2 of size 20. Maybe not the immediate reason but this may need fixing.

Andre

3

Also, if Andre’s suggestion isn’t it then try disabling DBMS Output polling in Toad and executing (or comment out your DBMS Output statements). There was a bug with Unicode characters in Toad’s DBMS Output polling. See… www.toadworld.com/…/31664

1 Like
4

Andre,

Thanks for forcing me to look again at the definition of SUBSTR !! you are correct, and I missed this, thinking it is from stat position to end position. In reality it is from start position and how many characters to select.

Regards,

Sami

From: Andre Vergison [mailto:bounce-avergison@toadworld.com]

Sent: Tuesday, November 29, 2016 3:06 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] ORA-06502: PL/SQL: numeric or value error: character string buffer too small at line 24 [EXTERNAL]

RE: ORA-06502: PL/SQL: numeric or value error: character string buffer too small at line 24

Reply by Andre Vergison

At first sight:

v_udf_c108 VARCHAR2(20);

IF k.sif_len > 20 THEN

v_udf_c108 := SUBSTR(k.sifno, 21, 40);

Something up to size 40 won’t always fit in a varchar2 of size 20. Maybe not the immediate reason but this may need fixing.

Andre

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Samiyou’re welcome. Did you feel it as “forcing”, it was rather meant as a kind suggestion … :slight_smile:

Glad it works.

Andre

6

Andre,

I mean that I forced myself to revisit the description and not to rely on memory, as I am sometimes mixing languages and methods and procedures… :slight_smile:

So Thanks for your help. I appreciate it.

Regards,

Sami El-Murr.