This content has been marked as final. Show 2 replies
A deterministic function will return the same value given the same parameters, regardless of user, so your case does not really fit.
However, the current implementation of deterministic is such that this shouldn't make a difference - but that may not be always true going forward.
Then again, it is only when used in SQL that this declaration of deterministic should be relevant anyway
When using the a plsql function in SQL, where the function is expected to return the same values for the same parameters and you want to minimise the number of calls to the function, the benefits of scalar subquery caching are more consistent across versions and more predictable, i.e.
This should result in one execution of the function regardless of rows returned by the SQL.
WHERE UPPER(tab1.column1) =(SELECT package1.getusername() FROM DUAL)
This package is also used to get parameters from the context.But in SQL statements it would be better to reference the CONTEXT directly using SYS_CONTEXT.
Using a package only introduces more unnecessary context switches between SQL and PLSQL engines.
WHERE UPPER(tab1.column1) = SYS_CONTEXT(<your_context>,<your attribute>)