Unlike Oracle's earlier connect-by clause, recursive CTEs were designed with fixpoint semantics from the beginning.
Without common table expressions or connected-by clauses it is possible to achieve hierarchical queries with user-defined recursive functions.
[7] A common table expression, or CTE, (in SQL) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs can be thought of as alternatives to derived tables (subquery), views, and inline user-defined functions.
[18] An example of a recursive query computing the factorial of numbers from 0 to 9 is the following: An alternative syntax is the non-standard CONNECT BY construct; it was introduced by Oracle in the 1980s.