In a SELECT expression, the WHERE clause is executed before the ORDER BY clause. Consider this example:
DECLARE CURSOR recent_users IS SELECT user.name, user.creation_date FROM user WHERE ROWNUM <= 5 ORDER BY user.creation_date DESC; BEGIN ...
This query won't return the last 5 created users. The database will filter the users without any ordering and after, apply the ORDER BY clause. The corrected query is:
DECLARE CURSOR recent_users IS SELECT name, creation_date FROM (SELECT user.name, user.creation_date FROM user ORDER BY user.creation_date DESC) WHERE ROWNUM <= 5; BEGIN ...