Some time ago, I already published a blog posting about the new PL/SQL WITH clause which has
been introduced in Oracle12c. Today, I'll blog about this again, since this topic has some fascinating
aspects ...

Short recap: The PL/SQL WITH clause has been introduced with Oracle12c (12.1.0.1) and it allows
to define PL/SQL functions in the WITH clause of a SQL query. These functions only exist during
query execution - there are no dictionary objects. As soon as you can execute a SQL query, you
can start embedding PL/SQL into that query. Here's a very simple example:

The result of that very query could have been achieved without PL/SQL WITH as well, but it's here
as an example: In the WITH clause, a PL/SQL function named GET_SAL is being defined - and used
in the main query - the PL/SQL is very simple (and useless in this case as well), but we can start
doing other things. Next, I'll start to play with EXECUTE IMMEDIATE.

Again, we define a PL/SQL function named GET_ROWCNT. This function uses
EXECUTE IMMEDIATE to execute another, dynamic SQL query. This single query
actually executed as much SQL queries as there are tables in the database schema. Is
that static or dynamic SQL ...? That is the question.

Let's go further with this. We could try to do a SQL UPDATE as part of our PL/SQL function. Here's
the slighly changed first example, based on the table EMP.

That is interesting: a SQL query, which actually does some Data Manipulation. Beginning
with Oracle12c, a simple SQL query can do a lot of things. But what, if that behaviour is
not wanted ...? In most cases, the SQL is being controlled by the application - users cannot
execute any arbitrary SQL - and rightly so.

But as soon as an application offers some kind of freeform SQL window, we need to think
about the PL/SQL WITH clause. What can we do, that users can execute SQL queries, but not perform
any PL/SQL logic or DML operations ...? We have two approches:

First and most important: The database session must have only those privileges which are
absolutely required to do its work. It's best practice for years, that the database connection
is not done as the table owner, but as a low-privileged connection user. And this
user even doesn't have privileges on the tables directly, but only SELECT privileges on
some views and EXECUTE privileges on some PL/SQL packages containing the business logic. In
such a setup, the PL/SQL WITH clause could be used, but one cannot damage anything.

Another level of protection is within the PL/SQL WITH clause itself. You can only add PL/SQL code
to your WITH clause when you code the top level query. Within a subquery, PL/SQL code cannot
be added to the WITH clause. That means, when an application is about to provide a freeform SQL window
to its users, the user input should be embedded by SELECT * FROM () so that it
becomes a subquery. The end user now can execute arbitrary SQL queries, but no PL/SQL anymore.
Take care: You can add the /*+ WITH_PLSQL*/ hint to the top level query and thus make PL/SQL code
in subqueries work again. But this should be used with care - or (even better) not at all.

In summary, the new PL/SQL WITH clause makes SQL queries in Oracle12c much more powerful. The difference
between static and dynamic SQL becomes rather blurry and vague. When you are about to add some
freeform SQL window to your application, please think twice about which end users to
give that privilege and to which not. Of course, it's always best to have the application
designed by well known best practices, when the privileges are missing a PL/SQL WITH clause
cannot damage that much.

Most important to know about the WITH clause is that it does not work in a subquery. This
is not simply a missing feature, but an important aspect of database security. You can actually
use this fact in order to make your freeform SQL window more secure: Simply embed the
users' input as a subquery. BTW: That is the reason why PL/SQL WITH clauses can work within an
APEX "classic" report, but not in an interactive report.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.