ORDER BY usage in subquery (derived table) SQL standard allowed or not
A cursor is not a table, it refers to a table or a view of that table.  The cursor has an order only to the extent that the query that defines the cursor has an order by.  A subquery within that larger query would not define the order, and the fact that the query is being used for a cursor doesn’t make an order by in subquery legal syntax.

Basically, as with any other query, if you don’t have an order by on the outermost layer, then the optimizer is free to return the results in what it deems is the easiest/quickest way. It may take advantage of indexes or caches or anything else that allows it to produce the correct result, but is not constrained to do so.