Toad World® Forums

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

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.

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

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

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.

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

Glad it works.

Andre

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.