4 ms·

Not to be a dick, but what kind of issues did you have with their import/export tools? As someone who has worked with Oracle and Microsoft SQL databases for ma

Not to be a dick, but what kind of issues did you have with their import/export tools? 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.

bane 10y ago
It's a fair question. Keeping in mind that we literally had nobody who'd ever used Oracle in their careers (or at least beyond fairly basic uses a decade ago)...we had a fairly simple set of tables, one of which had a CLOB attribute. 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.
- aNoob7000 10y ago
  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.

bane 10y ago

It's a fair question. Keeping in mind that we literally had nobody who'd ever used Oracle in their careers (or at least beyond fairly basic uses a decade ago)...we had a fairly simple set of tables, one of which had a CLOB attribute. 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.

aNoob7000 10y ago
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.