Create multiple Oracle temporary tables using WITH Clause

According to the Oracle docs, the WITH clause behaves much like a temporary table and is a great way to clean up lengthy SQL:

The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. WITH is a part of the SQL-99 standard. This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. Using the WITH clause, Oracle retrieves the results of a query block and stores them in the user's temporary tablespace. Note that Oracle Database does not support recursive use of the WITH clause.

This is exactly what I was looking for to solve a difficult task involving ROWNUM (ROWNUM post). What’s more, you are not limited to only a single “named query”. You can define as many as you want by separating them with a comma:

WITH query_name AS (subquery)

     [, query_name AS (subquery) ]...