Home » SQL & PL/SQL » SQL & PL/SQL » Using a procedure in a select clause
Using a procedure in a select clause [message #246623] Thu, 21 June 2007 08:55 Go to next message
TonSchoen
Messages: 24
Registered: June 2007
Junior Member
In a procedure I want to create an internal table. The content of this table I want to select in a query.

The procedure has also some input parameters.

I read something about

select table(<procedure_name>)
from .....

But I don't know how it works!

Can anyone help me please???
Re: Using a procedure in a select clause [message #246626 is a reply to message #246623] Thu, 21 June 2007 09:04 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't create a table, create a pipelined function.
SQL> create or replace type mytype as object (
  2    empno integer,
  3    ename varchar2(50)
  4    )
  5  /

Type created.

SQL> create or replace type myarray as table of mytype
  2  /

Type created.

SQL> create or replace function myfunc return myarray pipelined
  2  is
  3  begin
  4    for rec in (select empno,ename from emp)
  5    loop
  6      pipe row (mytype(rec.empno,rec.ename));
  7    end loop;
  8  end;
  9  /

Function created.

SQL> select * from table(myfunc);

     EMPNO ENAME
---------- --------------------------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Regards
Michel
Previous Topic: utl_file.put_line
Next Topic: Read Column name in select query
Goto Forum:
  


Current Time: Sat Dec 10 18:41:02 CST 2016

Total time taken to generate the page: 0.38411 seconds