Hi All

Please look at following PL/SQL

declare
cursor c1 is select empno from test;
var_empno varchar2(4000);
begin
for c2 in c1 loop
var_empno := var_empno||' '||to_char(c2.empno);
end loop;
dbms_output.put_line(var_empno);
end;
/

The above PLSQL returns output correctly as
100 200 300 400 500

However if I use it in function it displays error. The function is as follows :

create or replace function testfunc return varchar2 is
cursor c1 is select empno from test;
var_empno varchar2(4000);
begin
for c2 in c1 loop
var_empno := var_empno||' '||to_char(c2.empno);
end loop;
return var_empno;
end;
/

SQL> exec :c:= testfunc
begin :c:= testfunc; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

Why the error above is occuring ?? I have even tried by removing that to_char() but still error is occurring. Is there any problem associated with VARCHAR2 returning from a function.

I am using Oracle 8.0.5;

Amol