Link to Original Source
Most of the things you ask about can be done with CSV as long as it's quoted properly. If it's not quoted properly, then it would be considered invalid. There's a nice RFC spec for it here: http://www.ietf.org/rfc/rfc4180.txt
What happens when your data contains \r or \n characters?
It's perfectly acceptable as long as you quote it (#6 example of RFC 4180). If Oracle doesn't support that, then I would say their implementation is broken.
What happens if the data has commas in it, and the
What do you do if your data is more complicated than a simple table?
I'd need a better example from you, but you can embed a csv record inside a csv field. It starts to get complicated really fast with all the "escaping" that needs to be done with the double-quotes. Such as something like a record containing "Last Name","First Name","Sub-Properties". The Sub-Properties could be embedded data such as sex, age, and height. For example:
Clearly, you can represent tree style data with CSV, but it has more flexibility than you think. Too many people roll their own CSV, because it seems so simple. Then they don't quote and escape quotes properly blaming any issues on garbage data.