You've got some dynamic application where you want to access some meta data from a ref cursor? Two examples are right here!
When you work with views like v$session you'll often want to know the current session ID. Here's how you can get it.
Make functions in queries faster by declaring them as deterministic