Using a pipelined function with parameters in a view

In the Oracle database you can use pipelined functions to improve performance or decrease complexity. With the table function a pipelined functions can be called:

select * from table(pf_demo(10));

This way it is not possible to wrap the parameterised pipelined function in a view. You can only create a view for a specific parameter value:

create view pf_demo_view as select * from table(pf_demo(10));

This is not very useful. Luckily there is a way to pass parameters to a pipelined function within a view. This solution uses package variables to store the parameter values and setter functions to set the values.
In our example we create the pipelined function in a package pf_demo_pkg. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. This way you can query the
view from PL/SQL.

Now there is also a solution to use such a view from SQL. This solution utilizes the fact that the where clause of a query is interpreted before the from clause. So when we call the setter(s) in the where-clause the view will pass these parameters to the piplined function. You can see this in action in the code below: