Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276843] Fri, 26 October 2007 07:47 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi All,

While running the following query I am getting error(although the variable length is more than the length of output).Any help will be appreciated.

SQL> CREATE OR REPLACE FUNCTION get_notes (p_pk  in temP_msgs.pk%type)
  2    return varchar2
  3    is
  4     l_notes1  VARCHAR2(32767) := NULL;
  5     BEGIN
  6      FOR cur_rec IN (select NOTES
  7       from temp_msgs
  8       where pk=p_pk)
  9       loop
 10               l_notes1 := l_notes1 || ',' || cur_rec.NOTES;
 11      dbms_output.put_line ('length = '||length(l_notes1));
 12       END LOOP;
 13  --     insert into temp_adiga values (l_notes1,1);
 14      dbms_output.put_line ('length after loop= '||length(ltrim(l_notes1,',')));
 15      RETURN LTRIM(l_notes1, ',');
 16      END;
 17  /

Function created.

Elapsed: 00:00:00.01
SQL> select  get_notes('CLIENT NUMBER=8840') from dual;
select  get_notes('CLIENT NUMBER=8840') from dual
        *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WW_ADMIN.GET_NOTES", line 15


length = 3000
length = 6001
length = 9002
length = 12003
length = 14386
length after loop= 14385
Elapsed: 00:00:00.04


Thanks & Regards,
Karisma
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276844 is a reply to message #276843] Fri, 26 October 2007 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Multibyte or varying length character set?
Add lengthB values in your output.

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276847 is a reply to message #276843] Fri, 26 October 2007 07:59 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Its not a multibyte character set.
Tried with lengthB ,getting the same result

Thanks,
Karisma
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276849 is a reply to message #276847] Fri, 26 October 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Its not a multibyte character set

On both side?

Quote:

Tried with lengthB ,getting the same result

Copy and paste, you did it in first post you are able to do it with this modification, post with both length and lengthB in the same dbms_output.put_line.
And post your version (4 decimals)

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276858 is a reply to message #276849] Fri, 26 October 2007 08:40 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Your function cannot return a VARCHAR when the number of characters/bytes exceeds 4000, this will always issue an error ORA-06502

The total length was 14386 characters, and the number of bytes will be equal or greater than that number.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276860 is a reply to message #276858] Fri, 26 October 2007 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course! I didn't see he called it from SQL.
4000 bytes is the limit for SQL VARCHAR2, return a CLOB.

Regards
Michel

[Updated on: Fri, 26 October 2007 08:42]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #277112 is a reply to message #276860] Mon, 29 October 2007 00:08 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi,
Thanks a lot for all of your replies.
Yes,i was calling from sqlplus.I'll try with clob.
Thanks & Regards,
Karisma

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #277121 is a reply to message #277112] Mon, 29 October 2007 00:32 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Yes,i was calling from sqlplus

I said SQL not SQL*Plus, any SQL client will have this limit which comes from Oracle SQL engine.

Regards
Michel
Previous Topic: Table Output
Next Topic: Showing "Insufficient Privileges" while executing DBMS_AQL.LISTEN
Goto Forum:
  


Current Time: Wed Dec 07 10:43:00 CST 2016

Total time taken to generate the page: 0.06249 seconds