PL/SQL - Using a function as a table

Occasionally, there is a need to output the data of a function in such a way it can be used as a table. In a recent project, we were wrapped the output of a "c" external procedure using this feature. For instance:

1. select * from some_func();  

The syntax there isn't exactly correct, but that is the jist of what we want. To accomplish the following, you need two things. A user defined type of type table and a pipelined function. Below is a very simple example:

1. create type num_as_table as table of number;  
2.   
3. create or replace function work_as_table  
4. return num_as_table pipelined  
5. is  
6. begin  
7.   pipe row(1);  
8.   return;  
9. end;  

Now the above function, while not very interesting does demonstrate the point. I created a new type called, "num_as_table" which basically represents a number. I then created a pipelined function that simply pipes the number 1. Now I can simply query my function using the following select statement:

1. select * from table(work_as_table());  

As we would expect from that snippet, the output will be one. Obviously, this is a very powerful tool as you can do some heavy duty data manipulations then pipe it out as if it were a table. (Note: I have only done extensive testing of this on Oracle 10g, however it will work on 9i as well.)