I have a MYSQL and MSSQL background. I never understood everyone's fear of using CLOB/BLOB data types. Then I started working Oracle.
Sorry to break this to you but CLOBs and BLOBs are sort of a magical type field. They are a special kind of field that can hold up to something like a couple of gigs of characters or binary data in them. I'm not a PostgreSQL or MySQL admin but I would assume that you would have the same issues with those database pumping data in and out of CLOB/BLOB field type from database versions that don't match.
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.
> 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.
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.