Re: Stored procedure returning records

thorsten.kettner_at_web.de (Thorsten Kettner) writes:

> Hi all,
>
> I would like to write a stored procedure that returns records and use
> that like I would use a table or view. For example:
>
>   select emp_no, dept_no
>   from emps_hired_in(2003)
>
> where emps_hired_in is a stored procedure. We have changed recently
> from Ora 7 to 9i, and I am almost sure this was not possible in Ora 7.
> Is it possible with 9i? If so, how?

You can use pipelined function. Here an example:

,----
| connect scott/tiger;
|
|
| create type pipe1_typ as object (
| empno number,
| ename varchar2(30)
| );
| /
|
| create type pipe1_tab as table of pipe1_typ;
| /
|
| create or replace function pipe1 return pipe1_tab
| pipelined
| is
| cursor cur is select empno, ename from emp;
| eno number;
| ena varchar2(256);
| t pipe1_typ;
| begin
| open cur;
| loop
| fetch cur into eno, ena;
| exit when cur%notfound;
| pipe row(pipe1_typ(eno,ena));
| end loop;
| return;
| end;
| /
|
| select empno from table(pipe1);
| select empno from table(pipe1) where empno = 7499;
`---- Received on Wed Sep 10 2003 - 09:57:27 CDT