Here are some examples that should help with point 7 and 8. Example 1: my_pkg.my_function executes for every row due to no scalar subquery: select e.empno, my_pkg.my_function(p_param => 'Y') as results from emp e; Example 2: my_pkg.my_function called from a scalar subquery caches the result of the function; does not execute on every row: select e.empno, (select my_pkg.my_function(p_param => 'Y') from dual) as results from emp e; Example 3: There isn't really a benefit in this instance because empno is different for every row, however it's still a good habit: select e.empno, (select my_pkg.my_function2(p_param => e.empno) from dual) as results from emp e; Hope that helps! :)