horizontal output .... function execution display's error

[amolik]

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

[stancho]

Try

declare
c varchar2(4000);
begin;
select testfunc into c from dual;
end;
/

Also I think you should use
PRAGMA RESTRICT_REFERENCES (testfunc, WNDS);
in your function (not sure, but I think in 8.0.5 you should).

[amolik]

Thanks stan

However the error still occurs if I call it using exec :c:= testfunc. I have packaged that functiom

I am getting the result if I do something like

"select testfunc from dual"

Right Now I have asked VB people to use this query in their report writer but not really understood the logic behind not running this FUNCTION.

If I change varchar2 return type to number and then return some temporary no.s then it works very well with 'exec' function. So is there any problem with varchar2 return type only.

Amol