This Online Tech Support based on examples to be easier to follow. The Oracle with operator allows to re-use the same sql query or queries in your select statement. Using the select in Oracle in the with operator will keep your select smaller, cleaner and easier to amend. The basic Oracle With operator syntax is:
WITH <your_query_name> AS (SELECT <columns> FROM <tables>) SELECT <columns> FROM <your_query_name>;
The following example will show you how to write the most basic oracle select statement using the Oracle With:
WITH my_dual AS (SELECT * FROM dual) SELECT * FROM my_dual;
Basically we are declared an Oracle select inside the Oracle with operator and named it as “my_dual“. Later under the with operator we are using the new declared query named “my_dual” as it is a table. You can use the variable more then one time. On this next example we are going to re-use the same object three times to show you how it works.
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 query you defined 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 and make your SQL query to finish faster. The next example really does not need this Oracle hint, but for showing how to use it we have added it into operator 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 operator, 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.
The following WITH example show how to use the Oracle With operator 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 operator. 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 Online Tech Support 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 operator. 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 operator 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 came out quite huge because we did try to make it more flexible to use. The Oracle With operator takes 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 the last example shows you possible options writing select using Oracle With operator.
The following examples are extension of the last CSV text and in this case we are storing them in a table. To be able to show this example we are needing a new table and it is called CSV_TEXTS as on the picture below.
The table above has two columns ID for unique values and TEXT column for different coma separated strings. Now the SQL below is using the very same CSV_TEXTS table and we are looking for to split string of ID 2.
WITH my_texts(id,i,total,text,text2) AS
(SELECT cst.ID,
1 AS i,
LENGTH(cst.text) - NVL(LENGTH(REPLACE(cst.text, ',')), 0) AS total,
NVL(TRIM(SUBSTR(cst.text, 1, instr(cst.text, ',') - 1)), cst.text) AS text,
SUBSTR(cst.text, instr(cst.text, ',') + 1) AS text2
FROM csv_texts cst
UNION ALL
SELECT my_texts.id,
my_texts.i + 1,
my_texts.total,
TRIM(REPLACE(my_texts.text2,
SUBSTR(my_texts.text2,
NULLIF(instr(my_texts.text2, ','), 0)))) AS text,
SUBSTR(my_texts.text2, instr(my_texts.text2, ',') + 1)
FROM my_texts
WHERE my_texts.i <= my_texts.total)
SELECT /*+ MATERIALIZED myt */
myt.id, myt.text
FROM my_texts myt
WHERE myt.id = 2;
The “text5,text6,text7” string of ID 2 is splitted to 3 rows “text5“, “text6” and “text7“. The MATERIALIZED hint is used to reduce splitting process execution repeating if you would need to more than one ID values.
See Also:
Home Oracle Select