Every single export option available in Oracle SQL Developer truncated the CLOB field whenever it felt like, and half of the exported formats were exported without escaping content in the CLOB so that they were unparseable on import.
When we finally called in the Oracle consultants, they said "we never use the import/export tools in SQL Developer" and moved on to SQLLDR, but failed for exiting reasons last seen in 1980's computing like "Error in Index ORA-42321" and then other issues with the parser.
Data Pump didn't work for some reason only the consultants know, but "minor version mismatch" was something they liked to say.
I don't remember the final solution, but I believe it was a hackneyed combination of writing some python code to connect to one of the databases, dumping the table data to a bunch of INSERT statements then just copy-pasting the whole output back into SQL Developer and running it in chunks until everything could be inserted into the new table. We couldn't just write more python code to pump the data back in because of some permissions problem that nobody could ever quite resolve satisfactorily. We also couldn't just load the file through the SQL Developer because it would only load files that were sitting in a specific, inaccessible directory on that specific server, not through the GUI.
I think this was when the lightbulb went off in all of our heads and we collectively realized "it's this terrible on purpose"
Total shit show with hundreds of thousands of dollars flushed down the toilet for no additional capability, huge program delays and a good reminder why nobody uses Oracle for new starts on software these days, it's absolute garbage and a curse on any organization that sustains it.
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.
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.
As someone who has worked with Oracle and Microsoft SQL databases for many years, you sound like someone who has never worked with their database. Oracle databases might be expensive but you can move data between instances and schemas using SQLLDR or DataPump with ease.