This tutorial page is based on examples to be easier to follow. The Oracle WITH clause allows you to re-use the same sql query or queries in your select statement. Using the select in Oracle in the WITH clause will keep your select smaller, cleaner and easier to amend. The Oracle WITH syntax is:
WITH <your_query_name> AS (SELECT <columns> FROM <tables>) SELECT <columns> FROM <your_query_name>;
The first example demonstares how to write the most simple Oracle select statement using Oracle WITH clause:
WITH my_dual AS (SELECT * FROM dual) SELECT * FROM my_dual;
We did declare the Oracle select inside the Oracle WITH clause and named it as “my_dual“. Below the WITH clause we are using the new declared query named “my_dual” as a table. You can use the variable more then one time as we have written it in the next example there we are going to re-use the same object three times. To see how it works take a look at the following SQL query.
WITH my_dual AS (SELECT * FROM dual) SELECT * FROM my_dual UNION ALL SELECT * FROM my_dual UNION ALL SELECT * FROM my_dual;
Sometimes the defined query is too slow or you will need to re-run it quite many times. There is a way to keep the result in Oracle temporary space using Oracle hint “Materialize”. In addition, this makes your SQL query to run faster. The following example really does not need this Oracle hint, but to show how to use it we have added it into clause WITH in Oracle section.
WITH my_dual AS (SELECT /*+ materialize */ * FROM dual) SELECT * FROM my_dual UNION ALL SELECT * FROM my_dual UNION ALL SELECT * FROM my_dual;
You can also use the Oracle Materialize hint in a usual Oracle select statement without the WITH clause, but for your information quick queries does not this Oracle hint. It will make a query a bit slower while it saves the result into the Oracle temporary space and if your query does not take long time to finish using this Oracle materialize hint makes it even slower. The hint works reverse way with a slow Sql query where the first execution is still lower than usually but the next ones will finish much faster and through it your query will finish with less time.
In short, use the Oracle materialize hint only with slow Sql queries to gain better performance.
This example shows how to use the Oracle WITH clause with in recursive mode using input parameters. You may need this functionality to cut strings or re-calculate dynamically in loop. If you have look at our other exampled in this page then you may have seen our Oracle dual query with Connect By function. The same can be done using the WITH Oracle. See the following example:
WITH my_dual(n) AS (SELECT 1 FROM dual UNION ALL SELECT my_dual.n + 1 FROM my_dual WHERE my_dual.n < 10) SELECT * FROM my_dual;
This SQL query returns 10 lines, but as it is more complicated to write than Oracle dual table and Connect By, so we have been using the simpler query on our other oracle.ehelp365.com pages.
When you executed the last Oracle Select statement and it returned the following error:
ORA-32033: unsupported column aliasing
32033. 00000 – “unsupported column aliasing”
*Cause: column aliasing in WITH clause is not supported yet
*Action: specify aliasing in defintion subquery and retry
Then your Oracle database version does NOT support this type of queries and you are not able to use it. The column aliasing in the Oracle With clause has been supported starting from Oracle 11g. The same error is shown on the next picture and this is done by using Oracle 10g database:
Let see how to split a text using the WITH Oracle. The same variable is hard-coded in many places in the query, but using Oracle PL/SQL scripts you can define the text in a variable and use only the Oracle variable. The original text is “a,u,b,c” and this Oracle WITH clause helps us to split it in the way that every letter stays alone in different row. The result we are looking for is in column “T1” as you see in the following example:
WITH my_dual(n,t1,t2) AS ( SELECT 1, substr('a,u,b,c',1,instr('a,u,b,c',',')-1), substr('a,u,b,c',instr('a,u,b,c',',')+1) FROM DUAL UNION ALL SELECT my_dual.n+1, replace(my_dual.t2,substr(my_dual.t2,nullif(instr(my_dual.t2,','),0))), substr(my_dual.t2,instr(my_dual.t2,',')+1) FROM my_dual WHERE my_dual.n < length('a,u,b,c')-nvl(length(replace('a,u,b,c',',')),0)+1 ) SELECT * FROM my_dual;
This Sql query is quite huge, since we are trying to make it more flexible to use. The Oracle WITH clause has three IN-parameters and it is using them in the second part of “my_dual” query. The query splits the string to letters and returns it as it was a table. The n parameters has been used as an index to keep the track of all letters in side the string and it is used to know which one needs to be extracted.
Basically you can do something simpler, but this example will show you possible options writing select using Oracle With clause.