SQLLDR threw up a bunch of index and constraint errors that magically went away when we just copy pasted in the INSERT statements into the GUI tool. Why? Who knows, but we didn't want to spend another $400/hr figuring it out.
CLOBs shouldn't be some magical type that's impossible to serialize and deserialize. There's nothing particularly tricky about serialized text in almost any other technology context, except in magical Oracle-land where all the tools are broken and you can measure how many dollars each row of data is costing you.
We weren't doing anything particularly exotic, or sexy, just very very vanilla relational dB 101 stuff, and Oracle has managed to turn that into expert-level wizard secrets. It doesn't matter how Oracle internally works, if we have to know, especially for these kinds of simple use-cases. If you have to know, you may as well just write what you need yourself instead of relying on a tool.
I agree with stickfigure, defenses of this garbage end up becoming an indictment of it.
Based on what you were trying to do, maybe writing a simple program to read a record from schema A and insert it into schema B would have been easier and cost less.
Yup, that's basically the lesson learned. Oracle's tooling is terrible, it's just easier to write your own most of the time.
My experience is that the tools and the "experts" leave a lot to be desired.
I wanted to move schema and data from the customer's server to my server so I could troubleshoot and issue the users were having.
The customer had 3 full time Oracle DBAs and I contracted a few high priced consultants to help with this job.
It took 6 days to move it.
Oracle isn't hated because people don't understand how databases work, it is hated because it is difficult to work with. Things may be different now (we were using 10g) but it feels like the most basic tasks take days of work.
It sounded like you were trying to defend the Oracle product here, but it actually comes across as a sort of indictment.
This brings me to one of my pet peeves, programmers tend to trash databases because most of them don't understand how they internally work. If you ask most corporate developers to generate an explain plan and tell you what is going on in the output, most couldn't generate the plan.