horizontal output .... function execution display's error
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
Re: horizontal output .... function execution display's error
Try
Quote:
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).