In the last few database versions, Oracle has consistently extended the functionality of the WITH clause. Oracle Database 12c introduces the capability to add user-defined functions and procedures directly to SQL statements instead of creating them as separate objects:
The goal of this approach is to decrease the number of context switches between SQL and PL/SQL, and it does so up to a point. As of the initial release of Oracle Database 12c, there are some drawbacks:
- Coding fragmentation – The reason for using stored procedures is to have a single point of functionality. If you allow developers to create user-defined functions directly inside SQL statements, you may complicate the whole code maintenance process.
- PL/SQL limitations – PL/SQL does not currently support SQL statements having functions in the WITH clause at all. Although the same call wrapped in Dynamic SQL will work just fine, this is a significant inconvenience.
- SQL limitations If you like to use the WITH clause with functions anywhere other than in the top-level query, you need to include a special hint,
/*+ WITH_PLSQL */, on that top level. - Optimization limitations The DETERMINISTIC clause is ignored for WITH clause functions.
- Performance Much to the surprise of many Oracle Database 12c early adopters, adding the PRAGMA UDF clause to regular functions consistently outruns WITH clause functions (as shown in the previous example—0.06 instead of 0.07).
The ink is still not dry on this new WITH clause functionality. It is worthwhile knowing that it exists, but unless something changes in later releases, its usability is a bit questionable, especially when compared to the PRAGMA UDF alternative.
Leave a Reply