horizontal output .... function execution display's error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Dec 2000
    Posts
    255

    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

  2. #2
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91

    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).

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    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
  •  


Click Here to Expand Forum to Full Width