Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Problems with Oracle SQL

codingtofreedom.com

Post reply on HN
Print view View original

141–150 of 277 posts

Re: Problems with Oracle SQL

#141

Post by Philip-J-Fry »

HideousKojima wrote: post #70
torginus wrote: post #63

Earlier quoted context omitted.

I'm not sure why the hate against MSDN - at least for .NET, I found the documentation to be one of the best in the business -even a decade ago. I'd only put Go's docs ahead in terms of helpfulness. I cut my teeth writing .NET code, then switched to Java writing apps on Android - the step down in doc quality was quite significant.

MSDN isn't terrible, but for answers.microsoft.com 90% of questions are some outsourced foreign support rep telling the user to run sfc /scannow and not looking into the issue any further. Which makes it a huge pain for sysadmin type stuff, their developer documentation is much better.

Yeah that site is terrible. Spent a few days digging for solutions to fix an issue, always ended up back on that site. Always same old useless information.

I had to go to like page 10 of Google results to find some random sysadmin's blog which looked like it came straight out of 2005 and guess what? Problem is explained clearly, steps to fix it are laid out, sorted and wish I found the website 2 days earlier.

Re: Problems with Oracle SQL

#142

Post by tux3 »

adamzochowski wrote: post #121
postalrat wrote: post #33

Earlier quoted context omitted.

First you accept that null and an empty string are the same thing. Then you insert a null.

But they are not. Empty string means "we know this is empty", Null means "We don't know". For example, I have a second name. Some people don't have second names. And some records we might not even know if such exists. Null means "unknown". Empty string cannot be equal to null.

Bad software punishes you for doing things right.

Re: Problems with Oracle SQL

#143

Post by habeebtc »

torginus wrote: post #63
vjust wrote: post #14

It seems to be the general rule of thumb that, when you want to google for an oracle error message, then exclusion of both oracle documentation site, and developer forums is your first criteria on the search bar. Same applies to Microsoft (large parts of MSDN), and now Amazon Web Services documentation (at least some parts of it). That's the reason StackOverflow works - it really solves programmer's problems, instead…

I'm not sure why the hate against MSDN - at least for .NET, I found the documentation to be one of the best in the business -even a decade ago. I'd only put Go's docs ahead in terms of helpfulness. I cut my teeth writing .NET code, then switched to Java writing apps on Android - the step down in doc quality was quite significant.

Win32 and .Net docs are great!

Azure and Powershell docs leave a lot to be desired. A lot of the time parameters are vaguely documented, return values completely undocumented. You have to inspect the object returned by a lot of things to get to understand what members and methods it has, and what they mean.

Examples illustrating what formats it expects inputs in? Forget it.

Re: Problems with Oracle SQL

#144

Post by thornygreb »

irrational wrote: post #131
munk-a wrote: post #8

I've worked with databases for a while now and I've never advocated for moving to Oracle (I have advocated for postgres though). The long term organizational costs is a big factor - but an almost equally large factor is the fact that 1. I have never actually used their dialect and have heard absolutely terrible things about it 2. There is no non-enterprise DBMS available that uses something close to their dialect so…

The problem is the Oracle dialect was created BEFORE the SQL standard was written. For backwards compatibility they never changed it after the SQL standard was written. Personally, I think they should have had a flag so you could choose whether to use standard SQL or Oracle SQL.

Exactly, and many features that are in the standard now and we take for granted were invented by Oracle in the first place.

Re: Problems with Oracle SQL

#145

Post by tux3 »

marcosdumay wrote: post #80
jwitthuhn wrote: post #11

This is missing my favorite oracle quirk: Empty strings are equivalent to null. Trying to insert an empty string into a NOT NULL column will fail, which took me a while to understand the first time I saw it happen.

> Empty strings are equivalent to null. No, they are not. Empty strings are completely different from null, and if you go returning them or testing for equality, everything will break by random some single-digit percent of the time. The same for concatenating, taking the length or iterating. I imagine there's some deterministic procedure to decide what leads to an empty string and what leads to null. The one thing I…

Fascinating. Of course it would be too easy if they were strictly the same!

It seems like reading the tale of a greek programmer cursed by the gods to work with madness itself.

Re: Problems with Oracle SQL

#146

Post by MangoCoffee »

ericbarrett wrote: post #17
munk-a wrote: post #8

I've worked with databases for a while now and I've never advocated for moving to Oracle (I have advocated for postgres though). The long term organizational costs is a big factor - but an almost equally large factor is the fact that 1. I have never actually used their dialect and have heard absolutely terrible things about it 2. There is no non-enterprise DBMS available that uses something close to their dialect so…

Oracle sells to executives, not technical staff. Training on Oracle tech is via certified classes, provided “free” by your employer as part of their licensing deal; not online discovery. Source: sold to and worked with Oracle Inc. for several years, got a front row seat at the sausage factory.

>Oracle sells to executives, not technical staff.

agree. i have a year or two with Oracle Apex. at a pervious job, we were using Java to develop a web app but a executive saw how fast you can create a web page/app with Oracle Apex. we switch over since we already using Oracle for database.

Enterprise software is not targeting you (techies). its for CxOs.

Re: Problems with Oracle SQL

#147

Post by roflc0ptic »

I think I've found 5 bugs in the Oracle database in the last 4 months, including multiple queries that returned incorrect results.

They end up being in weird edge cases - one was... you needed to be casting something as JSON, parsing it, and have a WHERE clause that included a compound predicate. A similar but slightly different query simply threw an error. I made a full reproduction and everything. Got bounced around between a couple of departments until we finally reached an engineer who said, and I lightly paraphrase: "um, that seems weird. I don't see anything in the docs about it."

The solution to that was updating from oracle 19.3 to 19.12. But _that_ broke our INSERT IF NOT EXISTS style queries. We were using this "hint" they have, "ignore_row_on_dupkey_index", which is a comment that goes before your query which actually affects the code execution (WTF?).

Batch queries via JDBC return an array of integers: the length is how many different queries you batched together, and each element represents how many rows were affected by each batch.

Unfortunately, when using this ignore_row_on_dupkey_index, the array sometimes both 1. has the wrong length, and 2. has invalid integers: stuff like -1203214.

So we switched over to using MERGE INTO WHEN NOT MATCHED style syntax, and everything was hunky dory, right? Wrong. We started getting UNIQUE CONSTRAINT VIOLATED errors. As near as we can tell, MERGE INTO WHEN NOT MATCHED can still run into a race condition - if you have two queries that try to insert the same non-existent row, the database will check that the row doesn't exist, execute the queries, and then blow up one of them. But as far as we can tell, only on batch queries. As I'm not paid to figure out WTF is wrong with oracle, just get it to work, I didn't end up doing a full repro of this stuff. But there's a couple of weeks I'm not getting back.

Re: Problems with Oracle SQL

#148

Post by Joeri »

munk-a wrote: post #8

I've worked with databases for a while now and I've never advocated for moving to Oracle (I have advocated for postgres though). The long term organizational costs is a big factor - but an almost equally large factor is the fact that 1. I have never actually used their dialect and have heard absolutely terrible things about it 2. There is no non-enterprise DBMS available that uses something close to their dialect so…

Oracle is free to download and try out. It’s only when you want to use it in production that you get to know the heart of their business: the oracle sales team. They are pushy, and devious, and they never give up.

I spent about a decade working on a large system built on top of oracle. Once you get used to its idiosyncracies it is not actually that bad. It has a good query optimizer, so even badly written queries tend to perform ok. And if there is any DB feature you want, it probably has it. Whether you can afford that feature is a different matter.

Re: Problems with Oracle SQL

#149

Post by q-big »

irrational wrote: post #130

How can he write that long of a blog post about Oracle SQL and not mention the number one sin? Null is equal to empty string.

> Null is equal to empty string.

marcosdumay disagrees:

https://news.ycombinator.com/item?id=28484963

NULL and empty string are often equal, but sometimes not. Life would be boring otherwise.

Re: Problems with Oracle SQL

#150

Post by kubanczyk »

SketchySeaBeast wrote: post #32
vbezhenar wrote: post #15

Earlier quoted context omitted.

One of oracle quirks for me is varchar2. What happened to varchar1? Nobody knows.

Oh sorry, are you looking to insert 4001 characters in your varchar2? Buckle up kids, things are gonna get rough.

Or maybe 3999 chars, of which a couple are two-byte. More fun.
Post reply on HN