4 ms·

Based on your reply, you had a bad consultant and you used the wrong tool to load the data. Dealing with CLOB/BLOB fields can be a hassle sometimes but that sh

Based on your reply, you had a bad consultant and you used the wrong tool to load the data. Dealing with CLOB/BLOB fields can be a hassle sometimes but that shouldn't kill a project. Both Datapump and SQL-loader could have both been options. 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.

stickfigure 10y ago
There's a phrase to describe things that require you to understand how they internally work: leaky abstraction. It sounded like you were trying to defend the Oracle product here, but it actually comes across as a sort of indictment.
bane 10y ago
The real problem is that there's something like a dozen different ways to import/export data and all of them do something just a little bit different, even when their accessed via pretty much the same interface. Datapump didn't work because there was a minor version mismatch between two of the dBs, something like a single letter in the version. We weren't using any features at all that distinguished our use of one dB over the other, so the data should have migrated smoothly. 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.
- aNoob7000 10y ago
  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.
  - bane 10y ago
    > 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.
- ScottBev 10y ago
  I have a MYSQL and MSSQL background. I never understood everyone's fear of using CLOB/BLOB data types. Then I started working Oracle.
staticautomatic 10y ago
Even if this is true, it kind of misses the point. There's no reason such a simple task should require this much knowledge in the first place.
devopsproject 10y ago
> Based on your reply, you had a bad consultant and you used the wrong tool to load the data 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.

stickfigure 10y ago

There's a phrase to describe things that require you to understand how they internally work: leaky abstraction. It sounded like you were trying to defend the Oracle product here, but it actually comes across as a sort of indictment.

bane 10y ago

The real problem is that there's something like a dozen different ways to import/export data and all of them do something just a little bit different, even when their accessed via pretty much the same interface. Datapump didn't work because there was a minor version mismatch between two of the dBs, something like a single letter in the version. We weren't using any features at all that distinguished our use of one dB over the other, so the data should have migrated smoothly. 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.

aNoob7000 10y ago
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.
- bane 10y ago
  > 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.
ScottBev 10y ago
I have a MYSQL and MSSQL background. I never understood everyone's fear of using CLOB/BLOB data types. Then I started working Oracle.

staticautomatic 10y ago

Even if this is true, it kind of misses the point. There's no reason such a simple task should require this much knowledge in the first place.

devopsproject 10y ago

> Based on your reply, you had a bad consultant and you used the wrong tool to load the data 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.