Last August, I discussed this Oracle12 new feature, a pragma compiler setting, called User Defined Function, or UDF.
I sit in on as many sessions as I can from specific individuals. Bryn Llewellyn, “Distinguished Product Manager”, he is a senior person on the PL/SQL team at Oracle Corp…is one such person I try to make it to all of his presentations.
He discussed this feature in one of his New Features presentations and gave some insight as to how it works.
This feature is designed for functions you wrote that contain SQL. Internally, there is a context switch between the SQL and PL/SQL engines when the PL/SQL contains SQL…an internal hand-shake between the 2 processors…and this context switch does indeed take some time. Tom Kyte has material on his site about the amount of time this takes and how ‘inlining’ might be a better option than a function because of this context switch.
I’ll discuss inlining next week
Bryn says this new UDF feature is designed to not do this context switch. I initially thought that maybe internally the Oracle folks were converting these to some kind of table function but no; it’s the way the rows are being passed to the function but without the context switch.
I see no downside when you get to Oracle12, to use this feature on any function called from a SELECT statement, and when that function has an imbedded SQL statement itself.
It’s a pragma clause and it’s only for Oracle12.
Just put “ PRAGMA UDF; “ anywhere between the AS/IS and the BEGIN. Compile the function.
Give me feedback if you use this. I suspect it will help the SQL that is accessing a lot of rows from the database and applying a line function that you wrote.
Dan HotkaOracle ACE DirectorInstructor/Author/CEO
Please keep in touch with new infos.