Have you ever been in the situation to test a piece of code in Oracle and you don't have the required data. With the WITH-clause it is now possible to create the testdata without a lot of effort to create other tables. Look at this statement:WITH myTestTab AS (
SELECT 'Roger' FIRST_NAME,
TO_DATE('01.01.1970','DD.MM.YYYY') DAY_OF_BIRTH FROM DUAL UNION
SELECT 'Hoger' FIRST_NAME,
TO_DATE('01.01.1980','DD.MM.YYYY') DAY_OF_BIRTH FROM DUAL UNION
SELECT 'Noger' FIRST_NAME,
TO_DATE('01.01.1975','DD.MM.YYYY') DAY_OF_BIRTH FROM DUAL UNION
SELECT 'Doger' FIRST_NAME,
TO_DATE('01.01.1985','DD.MM.YYYY') DAY_OF_BIRTH FROM DUAL
WHERE DAY_OF_BIRTH BETWEEN TO_DATE('01.01.1969','DD.MM.YYYY')
The Blue section is the definition of the "virtual" table and the red section is the select-statement that accesses this "virtual" table.
You can easy simulate a table the table can also be named like the 'real' table where you have no Test-Data available. You want the Oracle-Documentation for this clause? I can't help you, i looked a lot in the whole documentation and got a lot of hits, but mostly they are about another with-clause (for materialized views) . If somebody finds the docu for that, please let me know!