Have you ever encountered a situation when you were in the middle of writing a SQL query and thought, “if only I could write a quick PL/SQL function for this, it would make this quick and easy?” But, unfortunately, you don’t have any privileges to create any functions in the schema. Luckily, since Oracle Database 12c, there is an answer for you.
With the release of Oracle Database 12.1.0.1 in 2013, Oracle introduced the capability of writing anonymous PL/SQL declarations as part of a SQL query. This is done with the WITH clause also referred to as Common Table Expression clause or CTE, which can now contain said anonymous PL/SQL declarations.
Let’s imagine for a second that you have a table products containing information about various sellers’ products. This could look something like this:
You would like to write a query that provides you, say, the product name, the domain where each product is sold and the URL. But you want to ensure that the first letter of the name and the domain name is always capitalized. Sure, you can do that in pure SQL, but you are comfortable with PL/SQL, and such functions are written quickly:
get_domain_name function
capitalize function
With this new feature, you can simply wrap both these functions into the WITH clause and reuse them within your SQL block, even multiple times:
To the database, this is just another SELECT statement with a common table expression. You do not need any write privileges on the schema for the user you are connected with: