How to simulate a view that receives arguments

Every DB developer knows this situation.
You create a general view that combine a bunch of tables and manipulate the data but doesn't filter for a specific business object.
In that case, you can't just select * from this view (because the tables are huge), you have to use a where clause:

select * from my_view a
where a.dept_id = :var;

And that work just fine!

This works like a view that receives an argument of dept_id, because the "push predicates" mechanism will first add this new filter into the view, and select by it.

Let's see what happens if we run this query, this time with more than 1 object for filtering:

    select * from my_view a
    where a.dept_id in (select dept_id from depts where manager_id is null);

In this case, there is a highly chance that the original explain plan of the view will "be messed", and in most of the cases that's not a bad thing.

But sometimes, the optimizer is not as smart as you, and won't realize that the IN clause contains only  a few records and it would better for it to just manage this query as if it was written like this:

select * from my_view a
where a.dept_id = :var1
union all
select * from my_view a
where a.dept_id = :var2
union all
select * from my_view a
where a.dept_id = :var3;

And use the original explain plan 3 times (or more), instead of using a new explain plan.

In other words, how can we simulate a view that receives an argument?

It's easy, and it worked for me many times.
Just use a function that return a table!
This function will receive an argument, execute the view's query with this argument, and return a table.

So now, go ahead and run this query:

select * from table(my_view_func) a
    where a.dept_id in (select dept_id from depts where manager_id is null);

And check performance.