Is there a nesting limit for correlated subqueries in some versions of Oracle?

Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1 level deep.

This works on all versions:

SELECT  (
        SELECT  *
        FROM    dual dn
        WHERE   dn.dummy = do.dummy
        )
FROM    dual do

This query works in 12c and 18c but does not work in 10g and 11g. (However, there is at least one version of 10g that allowed this query. And there is a patch to enable this behavior in 11g.)

SELECT  (
        SELECT  *
        FROM    (
                SELECT  *
                FROM    dual dn
                WHERE   dn.dummy = do.dummy
                )
        WHERE   rownum = 1
        )
FROM    dual do

If necessary you can workaround this limitation with window functions (which you can use in SQL Server too:)

SELECT  *
FROM    (
        SELECT  m.material_id, ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY resolution DESC) AS rn
        FROM    mat m
        WHERE   m.material_id IN
                (
                SELECT  con.content_id
                FROM    con_groups
                JOIN    con
                ON      con.content_id = con_groups.content_id
                WHERE   con_groups.content_group_id = 10
                )
        )
WHERE   rn = 1