Re: returning the contents of a table in plsql function

Oracle procedures can not RETURN non scalar types, because that violates the ANSI standard. If you need to return a results set from a stored procedure use a ref cursor as an in/out argument. Then you can fetch from it in the outter program / procedure.

Regards
David Russell

Craig Jennings wrote in message <6njdmm$7gf_at_bgtnsc03.worldnet.att.net>...
>A newbie question:
>
>I am trying to figure out a way to return the contents of a table (say
>scott.emp) in a plsql function. The reference books I have don't give
>examples of this. PL/SQL in 21 days even says that I can return a table as
a
>datatype just as I can integer or varchar2. This seems completely false
>judging from the error information.
>
>My next attempt was doing this using a cursor. It looked like this:
>
>create or replace function return_emp
> return cursor is
>declare
> cursor emp_table is select * from emp;
>begin
> open emp_table;
> return emp_table;
>end;
>
>Seemed logical enough, but this too failed. Obviously I am missing some
>information. If anyone can give me a code sample that works, I'd be greatly
>appreciative. All I am looking for is something that returns the contents
of
>scott.emp.
>
>Thanks in advance,
>
>-- Craig Jennings
>jennings_at_worldnet.att.net
>
>
>
>
Received on Sun Jul 05 1998 - 13:48:33 CDT