How to sort categorical values by an arbitrary order?
This can be done using a CASE statement in the ORDER BY clause:

WITH
Animals (name, category) AS (
 SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
 SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
  name, category
FROM Animals
ORDER BY
  CASE category
  WHEN 'CAT' THEN 1
  WHEN 'ELEPHANT' THEN 2
  WHEN 'DOG' THEN 3
  END
;


Please comment, if and as this requires adjustment / further detail.