Skip to Main Content
  • Questions
  • ORA-06502: PL/SQL: numeric or value error: character string buffer too

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, raja.

Asked: March 30, 2009 - 5:56 pm UTC

Last updated: April 21, 2009 - 2:05 pm UTC

Version: 9.2.0.8.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a package which concatenates the data and inserts into a file.
I am getting the error "ORA-06502: PL/SQL: numeric or value error: character string buffer too" while executing the package.

Please help me..

Thanks in advance.
I am using the following package and a function which concatenates the data with a comma, the function and package are below:

CREATE OR REPLACE FUNCTION concat_Str
(
p_cursor sys_refcursor,
p_del VARCHAR2 := ','
) RETURN VARCHAR2
IS
l_value VARCHAR2(32767);
l_result VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor INTO l_value;
EXIT WHEN p_cursor%NOTFOUND;
IF l_result IS NOT NULL THEN
l_result := l_result || p_del;
END IF;
l_result := l_result || l_value;
END LOOP;
CLOSE p_cursor ;
RETURN l_result;
END concat_Str;
/


CREATE OR REPLACE PACKAGE BODY pkg_test
AS
CURSOR c1(pFromDate DATE,pToDate DATE) IS
SELECT answer_code,
answer_text,
NVL((SELECT concat_Str(CURSOR( SELECT so.source_code FROM SR so, sy_sr ss
WHERE ss.survey_id = s.survey_id AND so.source_id = ss.source_id)) FROM dual), '') target
FROM survey s
WHERE s.rec_create_date > pfromdate
AND s.rec_create_date < ptodate
ORDER BY 1;


FUNCTION Target(vTrec IN SurveyTarget%ROWTYPE )
RETURN VARCHAR2
IS
vTargetRecord VARCHAR2(20000):=NULL;
BEGIN
vTargetRecord := vTrec.answer_code||'|'||vTrec.answer_text||'|'||vTrec.target;
RETURN vTargetRecord;
EXCEPTION
WHEN OTHERS THEN
RAISE ;
END;

PROCEDURE insertRecord(pTRecord IN SurveyTarget%ROWTYPE := NULL,
pFptr IN UTL_FILE.FILE_TYPE,
pTargetRecord IN VARCHAR2 )
IS

BEGIN
UTL_FILE.PUT (pFptr, pTargetRecord || CHR(13) || CHR(10));
UTL_FILE.FFLUSH(pFptr);
WHEN OTHERS THEN
RAISE ;
END;


PROCEDURE start_point(pFromDate DATE,pToDate DATE,pFileTarget IN VARCHAR2)
IS

vtargetRec VARCHAR2(20000):=NULL;
vFptr UTL_FILE.FILE_TYPE;
lFromDate DATE;
lToDate DATE;
BEGIN
vFptr := UTL_FILE.FOPEN ('OT_DIR', pFileTarget, 'w',32767);
UTL_FILE.FCLOSE (vFptr);
vFptr := NULL;
vFptr := UTL_FILE.FOPEN ('OT_DIR', pFileTarget, 'a',32767);
FOR v1 IN SurveyTarget(lFromDate,lToDate) LOOP
vtargetRecord :=convertForTarget(vTrec,pRunID);
insertRecord(pRunID,vTrec,vFptrT,vtargetRecord);
END LOOP;
UTL_FILE.FCLOSE (vFptr);
EXCEPTION
WHEN OTHERS THEN
RAISE ;
END;

PROCEDURE EXTRACT(pFromDate DATE, pToDate DATE)
IS
BEGIN
vFileName :='XYV_' ||TO_CHAR(SYSDATE, 'MMDDYYYYhh24miss')||'.txt';
l_decPoint := 'Call handleExistingRecords';
start_point(pFromDate,pToDate,vFileName);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END pkg_test;
/

and Tom said...

why do people do this?????

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;


what is the point, other than to make the code infinitely harder to debug. You know what you did by coding that?

YOU HIDE THE LINE NUMBERS FROM US. No longer can you tell where the error came from !!!!!


Tell you what, the error message is actually quite clear you have a variable somewhere in your code, in your code that *I* cannot run since I don't have your tables or any way to reproduce. And that variable is not wide enough to hold the data you put into it


when you delete all of the:

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

blocks - you'll discover immediately WHERE this is happening and then you can use your programming skills to correct it.


So, the error is

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

remove them all, don't code that again, and then you'll find out where to look in your code.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Gokhan Tuna, April 20, 2009 - 9:15 am UTC

I'm not supporting the "When Others Then Raise" usage; but isn't it better for pl/sql to improve its stack trace mechanism like stack traces of Java. For example; when the java code below is executed, the output simply contains the line number containing the error. Please note that this is just a simple and silly example to demonstrate my case.

By the way, I found this discussion from year 2004 to see that better stack traces are still required in pl/sql:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1932043078402

OUTPUT
java.lang.Exception: An error occured in division
 at OraTest.division(OraTest.java:15)
 at OraTest.main(OraTest.java:5)
Caused by: java.lang.ArithmeticException: / by zero
 at OraTest.division(OraTest.java:13)
 ... 1 more


CODE
public class OraTest {

public static void main(String[] args) throws Exception {
  try {
    division(5, 0);
  } catch (Exception e) {
    e.printStackTrace();
  }
}

private static final int division(int x, int y) throws Exception {
  try {
    return x/y; // line 13
  } catch (Exception e) {
    throw new Exception("An error occured in division", e); // line 15
  }
}
}

Tom Kyte
April 21, 2009 - 2:05 pm UTC

this is just a demo, never code like this, it is *stupid*

I think computer science courses should devote at least two semesters to "how to actually deal with errors", this is something that is never taught.


I don't know what you mean by referencing that link, that shows a way to capture more information - it doesn't seem to me to be showing a deficiency? But rather a feature...


ops$tkyte%ORA10GR2> create or replace function foobar( p_x in number, p_y in number ) return number
  2  as
  3  begin
  4          return p_x/p_y;
  5  exception
  6  when others
  7  then
  8          raise_application_error
  9          ( -20001, 'Error in division ' || chr(10) || sqlerrm ||
 10            chr(10)|| dbms_utility.FORMAT_ERROR_backtrace );
 11  end;
 12  /

Function created.

ops$tkyte%ORA10GR2> declare
  2          l_answer number;
  3  begin
  4          l_answer := foobar( 1, 0 );
  5  exception when others then
  6          dbms_output.put_line( sqlerrm || chr(10) || dbms_utility.FORMAT_ERROR_backtrace);
  7  end;
  8  /
ORA-20001: Error in division
ORA-01476: divisor is equal to zero
ORA-06512: at "OPS$TKYTE.FOOBAR", line 4
ORA-06512: at "OPS$TKYTE.FOOBAR", line 8
ORA-06512: at line 4


PL/SQL procedure successfully completed.



Error handling

Naresh Bhandare, May 11, 2009 - 12:20 am UTC

hello Tom,

<<I think computer science courses should devote at least two semesters to "how to actually deal with errors", >>


Perhaps a good topic for your blog - maybe a few posts on it to lay down your overall approach to it?

Thanks,
Naresh

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library