Select against functions or procedures

[chrisrlong]

If it is impossible, what is your suggestion when you have a situation where you find queries that are duplicated across several caller and want to put them in 1 place to be reusable ?

What I personally do is use Dynamic SQL. Instead of SELECT * FROM SomeFunc(), it becomes more like:

l_v_SQL := 'SELECT * FROM '||SomeBuildSQLFunc();

OPEN l_v_RefCur FOR l_v_SQL;

This has many advantages, including the ability to tweak the 'common' SQL in special cases. I can pass some parameters in that the function can act upon to modify the SQL for any special cases.