Re: returning the contents of a table in plsql function

Dear Craig Jennings.

    I am afraid you mix two different "tables". PL/SQL defines word "table" for two different premitives.

    First for normal SQL (relational) table and second for PL/SQL-table (Array-like structures).

    So it is possible to return from function PL/SQL-table (a la Array).     See chapter "PL/SQL tables" in "PL/SQL User's guide and reference".

Best ragards Michael S. Sidorov.

Craig Jennings wrote:

> 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 Wed Jul 08 1998 - 02:15:41 CDT