Oracle Database 12c PRAGMA UDF and WITH clause enhancements


Here are two interesting enhancements in Oracle database 12c PL/SQL.

PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can be invoked instantly in the SELECT statement while the procedures can be invoked from the functions used in the declaration section.

PL/SQL functions defined using PRAGMA UDF – SQL and PL/SQL have different memory representations of values. Therefore, interconversion is involved while “switching” from one engine to other and vice versa. It allows you to define the PL/SQL subprogram outside the SQL statement but matching the performance of an inlined PL/SQL program.

Let us do a small test to see the performance gains-

1. Created a test table T

PK integer not null,
n1 integer not…

View original post 684 more words