-
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
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).
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|