ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #276843] |
Fri, 26 October 2007 07:47  |
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
|
|
|
|
|
|
|
|
|
|