Problems with Oracle SQL
codingtofreedom.com
codingtofreedom.com
Most of those twenty years were spent doing my own things in Linux, Perl, and R. In glorious ivory tower isolation from the real world consequences of serving a master who's goal was to extract revenue...
Here I am for the last year serving Apple (the Beast from Cupertino?).
The problems are the same with documentation. Yesterday I found an example in some Apple documentation. It saved me half a day, and I almost dies from shock. I have never before seen an actual example...
Their main documentation is videos. Fucking videos. Videos of what looks like actors pretending to be developers, that go on for ages, have no transcripts....
And then the tools: At least the MS and Oracle tools work! (Do they?) In Apple you cannot believe what the debugger tells you about the state of your programme with out double checking in at least two other ways, because their tool chain is very buggy, and they will not fix it.
There is no money in fixing tools. We developers pay them SFA money, brighter colours on the App store, deeper dark arts in getting you to fork over $XX for shiny apps that is where their development resources are going.
SO... They are all the same: APple, MS, Oracle, Google.... We developers are their bitches, we will crawl over broken glass to get things done, so there is no need to spend any resources on us....
Come back RMS!! All is forgiven!!!
Thanks to you now I feel vindicated that even (Sr.?) Apple internal developer seems to feels that Apple Documentation should have examples although it's likely you were developing some obscure OS feature.
TBH I don't develop for the platform anymore, So I'm glad I don't have to put up with it anymore. I assume Swift has made development easier.
That's the reason StackOverflow works - it really solves programmer's problems, instead of the strange dialects of English and style that flourish on MS and Oracle and Java docs.
“How is babby formed?"
[EDIT] Corrected quote
I actually missed the original.
Sysadmin story. Recently I had a really strange problem after a disk migration - defrag.exe simply would not run no matter what (I needed it to TRIM the SSD), it just quits without any messages, as if it's /bin/false. GUI had the same problem.
The first search result was a thread at answers.microsoft.com, they give the same canned "sfc /scannow, dism /restorehealth, chkdsk /f" response. But OP finally solved the problem on their own, the "Optimize Drives" service was somehow been stopped, restarting it solved the problem. I tried it, it didn't work. Having no options, I decided to give the seemingly useless solution a shot (why did I bother to try? This was after a disk migration, I felt filesystem corruption could be real). It finally worked after "chkdsk /f"...
I was genuinely impressed. It was the first time ever that it actually fixed a real problem for me. Presumably defrag.exe detected a filesystem anomaly and refused to run.
Would be interested in the logic here, given that it runs counter to what I thought was extremely obvious advice by now.
Defrag generates writes to rearrange blocks in the _virtual_ view of storage visible to the OS, but even after defragmentation, the _physical_ placement is totally outside the control of anything except the firmware. If after defragmentation a file's blocks are arranged (0, 1, 2, 3) in the OS-visible view, in flash are still very likely to to be arranged (412, 77, 1, 12341, 5) etc. All you can do is let it know that some range of sectors is not used, which is what TRIM enables, and that does not require defragmentation upfront.
This is exactly what defrag.exe does on an SSD today. Since Windows 10 (or 8?), instead of doing a "real" defrag, defrag.exe also has an option to issue TRIM commands to unused blocks on an SSD. In other words, it works like fstrim(1). The time has changed and you must have missed the update. I suggest keeping your knowledge up-to-date before delivering a lecture on disk defragmentation on a forum where everyone should've known better.
https://docs.microsoft.com/en-us/windows-server/administrati...
> /d Perform traditional defrag (this is the default).
> /l Perform retrim on the specified volumes.
> /o Perform the proper optimization for each media type.
Since I migrated the hard disk using a block-level copy via "dd", it's a good idea to manually TRIM the disk afterwards to inform the controller about the unused blocks in the new filesystem for proper wear leveling (to the controller, the block-level copy looks like a single large file).
And before another one gives me another lecture: No, I did not corrupt the filesystem because I made the mistake of copying the disk while Windows was still in Fast Startup mode. It was probably ntfsresize(8), to be fair, the corruption was extremely minor.
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.
- It is was often unclear what version of the “thing” was in scope. An article from product version X will reference X-1 documention.
- Microsoft will gaslight you. If you interact with them on a significant issue, you need to snapshot their product docs. I’ve worked with Premier on issues when pushing products to the edge of their limits, and the product group will edit the product specs in near real-time.
That issue left a sour taste in my mouth. As a customer, I don’t really need to be in the middle of corporate politics, I have my own poisonous politics to deal with!
I share it because many folks can’t conceive that sort of thing being possible.
This was during .net 4.5 fyi, ages ago by now.
Powershell, though. I don't know how you could even document powershell. It's insane.
Does anyone know if there’s any sort of reflective documentation or anything in powershell? Like, is there a way to ask it what arguments exist for a command?
Also just FYI, it can fuzzy match on arguments, so you can specify only the first letter, or substring of the whole name. I think from this aspect, it is far superior to the UNIX tools.
Sometimes I write the same script as PS and as bash script. For PS I usually need 3x more characters than for bash.
There are some features though that are really useful. But most of the time you won’t know about them when you need them.
That's your mistake! Don't write bash scripts in PowerShell, and don't write PowerShell scripts in bash. Don't be surprised if the shopkeeper can't understand you, when you're speaking "French" by translating an English sentence word for word.
PowerShell is much more readable, terse, and elegant than Bash. It absolutely blows it out of the water... on Windows, where its inputs are the streams of objects that it's designed for. If you're trying to shoehorn text-based streams like in Linux into a PowerShell script, you're going to have a bad time.
See this earlier comment I made and the linked comments for some examples of PowerShell-vs-Bash: https://news.ycombinator.com/item?id=23423650
A sterling example of PowerShell's inanity is its brain-dead, ivory tower implementation of function return values. Anything that outputs to stdout gets added to the return value object for your function. Forgetting for a moment how non-standard and unexpected this is, consider that it’s impossible to completely silence many, many Windows commands CLI programs and utilities. Even with all the silent flags, output redirection, etc. it is simply impossible to silence stdout. What you end up with is... you just can't ever use return values in functions since it's so unreliable. This, coupled with PowerShell's atrocious performance (it's the worst performing scripting language I've used by far) instantly makes PowerShell a second class language for anything apart from very small scripts.
I've never heard anyone call PowerShell “terse” until today. It is currently winning the competition with Java for “Language whose inventor is most likely paid per keystroke”. PowerShell's arguments are extremely verbose and lines tend to become quite long as a result.
Disclaimer: I’m a recovering Windows Admin and haven't used PowerShell in a few years. I'm told that none of these problems have been fixed, but I don't know for a fact.
This is most obviously noticeable (to me at least), when debating ergonomics with people that prefer UNIX platforms, especially bash and text-based configurations. There was a study that showed that an action like moving a mouse to select a file feels slow because there's one slow movement, but selecting the file through typing at a console is perceived to be faster because there are many keystrokes in quick succession. People report that they prefer the latter for "the speed" even if it's an order of magnitude slower than the mouse if measured with a stopwatch.
The terse two-character commands of the UNIX world were an optimisation for teletype. As in a literal typewriter banging away on paper, at a rate of something like 10-30 cps. Modern (four-decade-old!) computers have tab complete, which makes this largely irrelevant.
Verbose commands are an enabler. They enable novice users to read scripts, instead of only masters being able to write them. Long, systematically and consistently named commands enable discovery through wildcard searches.
You cannot now -- nor ever will be able to -- do something like this in the Bash world:
Get-Command Get-Az*Disk*
It's a clever hack around byte streams that people have slowly built up over decades, evolving over time haphazardly. There was an 'sh' for example!
PS: You talk about using Python instead of scripting languages, which is actually a fine choice that I won't argue with. But have you considered writing "heavyweight" PowerShell modules in C#? As in, a proper DLL module? It's mindblowing how productive it is compared to trying to write a command line tool in C/C++, or any other language for that matter. Automatic input validation, input parameter name tab-complete, pipeline handling, all wired up with a handful of attributes...
For short scripts, this works great and improves discoverability for newcomers. This is the siren's song of PowerShell. However, the long commands and particularly the often unneeded/overly verbose parameters frequently creates a wall of text. This really hurts readability for everyone in all but the shortest scripts. Additionally, this wall-of-text that is all to common in PowerShell scripts is very intimidating to newcomers.
Regarding Tab completion in PowerShell, it frequently isn't terribly helpful. To work yourself up to something like Get-ItemPropertyValue is quite an incantation to remember, while being worse than a lot of bash ergonomics.
> have you considered writing "heavyweight" PowerShell modules in C#?
That is an interesting approach and would make life more bearable in MS-only shops. However, I can do the same thing with Python, which is superior to PS in so many ways and doesn't suffer any huge, glaring flaws (as PS does).
Filter is harder than it should be[0]. Same thing for map
[0]: https://www.concurrency.com/blog/august-2018/powershell-basi...
It's like they hire an army of noob-level interns who churn out "getting started" articles, with the end result being that you have to dig 5 pages deep into Google result to get meaningful documentation (4 of those pages are of people asking questions on Microsoft Connect or the-site-that-shall-not-be-named or something).
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.
In contrast, Win32 which has been around for over 25 years is mostly stable now.
More than once, the "solution" to my problem was a blog post by a SharePoint consultant from India describing some undocumented flag to pass to some obscure command, "but of course, you should never do this on a production system". (I bear no ill will towards Indian SharePoint consultants, to be clear. I just find it really creepy they seem to enjoy this kind of torment.)
Just get-help -full/get-member everything or export-clixml it. But maybe I've done it for too long, so I don't see the weaknesses anymore.
An example of a place where it's bad is the Python API for Azure. I needed to call some service, I forget which, and everything was clearly just translated from C#. There's a function which takes a string as an input, except it doesn't, it takes one of three string, neither of which is mentioned. I assume that in C# it's an enum, and Visual Studio will just list the option for you.
“if you know how to read it” is...not a ringing endorsement of documentation. But, its true that MS documentation is less likely to be wrong once you understand what it is trying to say than, say, Amazon's (though have glaring omissions or things concealed by opaque organization is quite common.)
While I'm sure the complaints are valid (or was in the MSDN days) I believe Microsoft is putting a lot of effort behind these pages. You can provide feedback on pages that will result in a GitHub issue being created. You can even make pull requests.
While it's possible to provide feedback not all areas seem to process this feedback in a timely manner which of course is frustrating. However, the good parts (like .NET) are very good.
I think my issue might be more with the English style. But why does SO work right-away for me, while other these other developer portals don't. Part of the flaw maybe lies with me - maybe these portals (like MSDN) need in-depth, patient reading (guilty here). SO on the other hand, is way quicker in helping to solve issues. Over the years though, I've had enough bad experiences ..
Someone should do a compare between SO and non-SO. Take a sample, discrete (if such a thing exists in software issues) issue, and see how both help to solve. The layout of the page, the noise, and finally the curated/voted answer all contribute. And factor in the fact that SO answers are written by a diverse group of people, many of them non-English speaking.
Call it hate, but regardless, Oracle product docs - when you're in a bind are the bottom of the pit.
Stack Overflow excels at showing implementation examples which, while the best way is not always highly rated, at least shows a way. When I was more junior I would sometimes take SO answers verbatim but I think now it at least gives me something to think about improving.
Because a list of errors that could occur and a comprehensive list of what is recoverable (and how) vs what should abort is not useful at all in developer documentation…
Just most links seem to be broken.
On the other hand, if you do understand the underlying network protocols and can read them (and find ways to view what is happening), it is like a super power.
A lot of times the people writing the code & designing the APIs for the complex parts of AWS, Java, .NET, Spanner, etc... are highly-technical and not exactly highly articulate.
And even the ones that are highly articulate usually have trouble figuring out who exactly their audience is and what their audience knows and to what degree they need to explain things.
And even the select few programmers who are good at this, run into another set of problems. The language they use amongst themselves is usually highly technical (because it reduces confusion and speeds up communication). However, many of the readers of the documentation aren't going to understand a lot of these terms or concepts. The language needs dumbed down.
Because this is time consuming and requires a lot of thought - the documentation is usually written by technical writers. While these people are usually good at figuring out the audience and how best to communicate with the audience - they have their own unique way of writing (for clarity) that usually causes the documentation they produce to feel non-concise and sometimes not even clear.
I think a good example of this is the Apigee documentation: https://cloud.google.com/apigee
After reading the page - I only have the vaguest idea of what it actually does and I have almost no idea when I should use it and when I shouldn't or how it actually works (which, tbf, probably isn't important at this stage).
StackOverflow is mostly useful to quickly (and often dirtly) solve issues that are burning now, without getting deeper understanding of the subject. This isn't universally true, some answers are exceptionally good.
!msdn will search MS's developer network
Iirc, the postgres docs were also good reading in a similar way: not concise, but a lot of good information
* Rust's examples work, because the default behaviour of Rust's automated testing is to test your documented examples (as well as any unit tests you wrote), so, it's actually more effort to write examples that don't work. If you're too lazy for that, you're going to not write any examples, so then I at least know I'm in uncharted territory.
* The relevant Rust source code is linked. Mostly. Rust's source links don't chase macros, so it's conceivable your link tells you that foo(X) is just the result of the macro make_thing!(foo,X) and you need to chase how make_thing!() is defined which is annoying. But 99% of the time you discover immediately what's actually going on.
This week I would say about half of my time was spent fighting with the C# library for talking to the Microsoft Graph API. Both of which are, in theory, "documented" and yet I repeatedly ended up cribbing from Stack Overflow answers or, after beating my head against a wall, pasting URLs (which I already know will go stale in a year or two) and Microsoft's uselessly bland explanations for the obviously broken stuff as the excuse for why we can't do things you would obviously anticipate being possible.
Today I particularly liked: There are five documented ways to make an educationClass. Most of them simply don't work (unanswered Issues on github), and the error responses for these methods are undocumented and lead nowhere. But one of them does work. However the C# library drops the output of the API call for that method on the floor, presumably because coping with this case was hard, and so the best option (as a Stackoverflow post explains) is to reach inside the library, dredge out the HTTP request it's about to do, and perform that request yourself, then do all the heavy lifting they couldn't be bothered to do with the HTTP response to get what you actually wanted (including a polling loop because apparently nothing after the 1980s happened for Microsoft).
However, in the months since that Stackoverflow post was written, the C# library API has changed, enough that the example code wouldn't even build.
The change is undocumented (of course) and involves an enumeration (also undocumented) which was auto-generated for some reason. This feels like somebody was hoping it wouldn't matter if they changed it, and that somebody was wrong. But if they'd been forced to document it then maybe they'd have either decided it wasn't worth it (still works as before) or I'd have saved ten minutes guessing how the API now works.
But now it's the weekend and I'm going to write Rust.
The two obvious things work, if you say the name of a header, you get that header, and there are constants like HOST defined for the most common headers to avoid typos, but you can do crazy stuff, and importantly if a hostile peer sends you crazy stuff Reqwest promises to cope.
Maybe it's that Rust's polymorphism is more explicit through Traits.
If Pigeon, Duck and Emu are all Birds, and further more Animals in C++ it's unclear what exactly this Bird superclass does for me, still less Animal or when I, knowing I have a Pigeon here, should consult the documentation for Bird and/or Animal rather than or in addition to that for Pigeon.
Whereas not only does the Emu not implement Fly, I can feel comfortable guessing that the code implementing Fly generically or for my Pigeon is unlikely to be related to my problem that this Pigeon says "quack" and I should instead look closely at the AnimalSound trait, given away by the fact I had to explicitly name that trait to get the "quack" noise from the pigeon.
So many reasons to use DataGrip, but often enough (>once a month) I have to fire up SQL Developer to get line number errors.
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 I figure actually hiring Oracle DBAs would be a nightmare.
These two factors combine to make me absolutely baffled at how completely Oracle has seemed to dominate every market but the private one (i.e. government, especially defense and educational). Have I been missing something about ways to actually get real world experience with Oracle?
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.
https://livesql.oracle.com --> A SQL scratchpad that also includes scripts and tutorials.
Oracle Live Labs (https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/home) --> Granted, while it's easier googled as "Oracle Live Labs" than to remember that URL, it does, however, provide free Hands-On Labs for users.
https://oracle.github.io/learning-library/ --> The instructions for the Hands-On Labs above and more can be found in the learning-library
asktom.oracle.com --> I fully free Q&A portal where you can ask Oracle Database employees for help. Furthermore, it offers regular "Office Hours" live webinars and an entire course of learning Oracle Database: https://asktom.oracle.com/databases-for-developers.htm
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.
A lot of the time when businesses by Oracle, they’re buying into the middleware rather than the RDBMS.
Siebel, PeopleSoft, BEA Weblogic.
I was the DBA for a Siebel instance for ten years.
I would say J.D. Edwards, but that's AS400/DB2.
SAP is also a big driver, although they don't want to be (so much so that they bought Sybase).
Sometimes with somewhat sensible reasons, like Oracle combining clustering and in-memory encryption, but it still means you don't get to choose...
Oracle it not that bad anyway, it's just stupidly expensive. I'd choose Postgres any day, but I worked with Oracle a lot and it never was a main culprit in my work. It has its issues, sure, but they're solvable.
Full of bugs, some that destroy speed, some that destroy data, some that just make a feature that you need unusable. More heavyweight than lead (although it gets fast after you throw enough hardware). Lacking any capable or usable management interface (but then, that excludes everyone except for postgres and mysql). Impossible to program. Impossible to predict how your program will run.... And my favorite, absolutely fragile, any wrong code you run there can take everything out of the air.
And yeah, stupidly expensive and comes with the Oracle legal team.
This would've been sufficient, honestly.
Did I say it has bugs?
Anyway the most recent pair I found on the wild was a problem that made indexes of georeferenced data fail at random, pushing your queries into a non-indexed search and breaking things like materialized views, and one that causes some inconsistency on testing clobs for null or empty string (what made them impossible to test for either when it applies). But there's a well known one that breaks optimization plans at random and goes with the last try (it doesn't matter if that it will scan that table 100000 rows 1000 times), and there's some bug where if you create some tables, populate them, drop them and repeat enough times you will lose your database. But that's just from the top of my head.
Oh, of course, that isn't including designed behaviors like the one that takes your database offline if you don't do backups often enough (where "often enough" is something that you can estimate but never be sure about its frequency).
https://www.theregister.com/2021/07/20/how_amazon_broke_free...
Sybase, Ingres
I worked at both a Sybase shop and an Oracle shop. I far preferred Sybase.
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.
Not that most Oracle DB ppl use it anyway.
Everybody has problems.
Which one? Good question. The mainframe, as/400, and Windows versions are all on separate source code last I heard.
https://www.enterprisedb.com/news/enterprisedb-and-ibmr-coll...
Having used many RDBMS, one thing I very much liked about Oracle was the bitmap index. Very very useful for some performance issues that a b-tree cannot solve.
Oracle was massive in the private sector 10 years ago, and it probably still is once you dig past the surface layer of nosql
Don’t forget that for a long term it was either Oracle or DB2
thats quite beautiful in its scale of monstrousness
If you look at how companies like NVIDIA do things, they throw ungodly amounts of compute at development and simulations. Entire data centres worth!
"Sounds like ASML, except that Oracle has automated tests.
(ASML makes machines that make chips. They got something like 90% of the market. Intel, Samsung, TSMC etc are their customers)
ASML has 1 machine available for testing, maybe 2. These are machines that are about to be shipped, but not totally done being assembled yet, but done enough to run software tests on. This is where changes to their 20 million lines of C code can be tested on. Maybe tonight, you get 15 minutes for your team's work. Then again tomorrow, if you're lucky. Oh but not before the build is done, which takes 8 hours.
Otherwise pretty much the same story as Oracle.
Ah no wait. At ASML, when you want to fix a bug, you first describe the bugfix in a Word document. This goes to various risk assessment managers. They assess whether fixing the bug might generate a regression elsewhere. There's no tests, remember, so they do educated guesses whether the bugfix is too risky or not. If they think not, then you get a go to manually apply the fix in 6+ product families. Without automated tests.
(this is a market leader through sheer technological competence, not through good salespeople like oracle. nobody in the world can make machines that can do what ASML's machines can do. they're also among the hottest tech companies on the dutch stock market. and their software engineering situation is a 1980's horror story times 10. it's quite depressing, really)"
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 know is that if you insert it on a table, you will always get null.
It seems like reading the tale of a greek programmer cursed by the gods to work with madness itself.
It just needs to be enabled: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-093...
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.
We need that again, another new 0 concept to add to 0, to distinguish between "set-to-0" and "not-yet-set".
Maybe 2 new concepts, since null is also different from 0. 0 is a value, null is the absense of a value.
Not just as an idiom or implementation detail in a programming language, but as a general concept that may be used anywhere in life.
Without it, we have exactly these confusions and ambiguities and differences of opinion about how to do something or what something means or what something should mean.
When it comes to datatyping, (the whole point of data types in a db), null is its own datatype, so forcing the allowance of nulls to get blank strings is kinda stupid and only causes software/application level bugs.
For real though, no idea. I'm glad that I never had to touch Oracle.
....
sorry
not possible (can't store empty string in a NULL col either)
for reals
(I once worked maintaining a MUD that used internal memory management and marked block terminals (which were unnecessary since it stored the length of blocks it had allocated) with ZZZ)
> select count(*) from mytbl;
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
Well, OK then...
> ALTER TABLE mytbl DROP COLUMNS CONTINUE;
SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-01654: unable to extend index SYS.I_OBJ4 by 128 in tablespace SYSTEM
The table was completely unusable, we needed to restore the DB from backup.
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.
I even remember the "Learn Oracle at the Sea" advertisement.
But that's the state of affairs and I try to avoid them as much as I can. Amazon seems to think similar [0].
His answers were sometimes prime examples of what is today known as seriously unwelcoming. But I remember I un-learned the "best practices" meme reading one of his answers. In fact it was just one sentence, some concise version of "if the 'best' setting existed, nobody would bother to make it a configurable option". But it clicked.
Burleson's more advanced answers were of limited usefulness, because they have never mentioned the exact oracle version. They often have been only valid for antiques like Oracle 7.
The infamous MSSQL "string or binary data will be truncated" error (fixed a while ago[0]), or the way either deals with unqualified access to columns which leads to surprises (e.g. in MSSQL subqueries or in Postgres's SECURITY DEFINER procedures).
[0] https://docs.microsoft.com/en-us/sql/t-sql/database-console-...
Oracle seems to have more than most, IMO.
I have a journeyman data engineering level experience with each. Rare combo I guess, Data Scientist focus.
Also, I had to switch to DBeaver since Oracle SQL Developer made things worse with random crashes and freezes. Back when I used it, an easy way to freeze the editor was connecting to a DB via VPN and then suddenly disconnect. The connection gets stuck, parts of the UI get frozen and after a while the whole thing freezes.
MySQL/MariaDB and Postgres are better.
Having to install and maintain Oracle software is the real hell. You sometimes need a patch for the installer (I kid you not, the OAM 12.2.1.4 installer needs a patch to work on Oracle Linux). The syntax for Advanced Rules in OAM is not completely described in the manual, the examples are wrong, and you have to disable the parser in version 12.2.1.4 because it is doesn't work.
It seems every little task turns into an investigation into a series of bugs, errors, and undocumented features.
Most other databases, the application includes a library that communicates to the DB via a protocol implementation... Oracle doesn't want this kind of use case.
Two patch sets must be applied to the nonfunctional installer before it will work, which was quite hostile to learn and master.
At least with 11.2, a new install set was issued for RedHat 7.
These errors tell you something positive about the existence of something you don't have the rights to know about and that is a defect in my opinion.
I not saying good error messages are not valuable or that Oracle's are fine, but the answer should never be that error messages tell you information about something you don't have permission to know about.
If your database error messages are exposed to users that actually are a threat ... that already seems like a world of pain.
For logged in users I would prefer logging with explicit error messages. Like that you can tell if someone is poking around or was hacked. And still get clear error messages.
However, this thinking comes at the cost of UX (or Developer eXperience). Much more mundane instance of the same thinking is hiding elements of the UI you are not allowed to use. This often gets me thinking - is there a way to do this that I'm not allowed to see, or is it just that I can't find the function in the UI?
A solution for the DX issue is logging the real error somewhere only accessible for an admin. Has this been implemeted anywhere in the wild? For UIs, just be honest and show the menu items disabled.
> Table does not exist or you do not have access. Please contact your DBA if it should exist.
For login, assuming sql*plus
> Either you have an invalid password, user does not exist, or database cannot be found at this endpoint. Please contact your DBA if it should exist.
Wikipedia article: https://en.wikipedia.org/wiki/David_DeWitt#The_%22DeWitt_Cla...
It's plainly illegal, but nobody who has built their business on Oracle DB wants to piss off Oracle the company with a lawsuit, and it doesn't appear any legal activists want to either.
[0] https://www.brentozar.com/archive/2018/05/the-dewitt-clause-...
Probably a security issue. If you tell a user”“table T exists, but you don’t have rights to write to it because you can’t increase sequence S”, you’re leaking the information that a sequence with that name exists.
It’s the same reason a good login system will say “invalid username or password” instead of “invalid password” and its recovery screen “if that’s a valid user name, a mail has been sent to the address associated with it”.
It is also a bit of an issue that they don't have a default IDE, I worked with Rapid SQL which is a clunky, slow and riddled with bugs IDE. I think the Rapid part of its name is pure gaslighting.
T-SQL is just so a weirdly ugly language and the feature set of SQL Server is super weird.
If we can, we go with Postgres or MySQL.
That said, still inclined to use PostgreSQL. I do not like MySQL at all, and every single time I've used it, I feel cringes of absolute pain... starting with "utf8" isn't UTF8, "utf8mb4" is. Or that indexing a binary field isn't case-insensitive (ie, binary) by default.
but as others mentioned, it's sold to executives, not to technical personnel.
Pros:
- pl/sql (I like it more than other flavors of sql, maybe because it was the first one I used a lot)
-pl sql developer. Not the java one (sql developer). I loved that you could see tabs in a list and in general it was smooth. I still keep a thin client in my spare drive and use it with delight from time to time
Cons:
-Installing and maintaining Oracle. But it wasn't terrible either
-Having to read Burleston posts. Not sure if this has improved
I know I am biased by my experience, I've heard many horror stories. I wouldn't choose Oracle today, but having to develop for it wouldn't scare me either
pl/sql is not a flavor of SQL, it is a name for the separate SQL-based “procedural” (imperative) language (hence the “pl”) supported by Oracle. The Oracle SQL dialect is usually referred to as “Oracle SQL” if there is a need to distinguish it.
It's amazing to see how calmly they handle managing Oracle, but their argumentation is also very Oracle-like: It's not a database problem, you're just using it wrong. Which would be a terrible answer, if they weren't right. I think most of us forget that for all the terribleness of Oracle, they actually do build a very stable and performant database, assuming you can afford it.
Error returned: "ORA-00907: missing right parenthesis"
Am I using Oracle wrong? No. It's a tool and CTEs are supported. Ergo the error message is an issue since it is not descriptive of the actual issue.
Vehemently disagree with the 'access denied' error message. Why would you want some one who does not have access to a table to know it exists.
I can code up SQL easily and find errors. I use sqldeveloper and granted It's not as helpful as say coding Dart on Android Studio IDE in pinpointing where a problem is and making suggestions. But I've been able maintain single sql statements 800 lines long, perform knapsack in a single sql statement, and develop complex packages in Oracle.
If you really want to see super complexity, download and create an Oracle EBS 12.2 virtualbox instance and checkout the PL/SQL packages in the APPS schema and the schemas themselves of which there are over 200.
I've used NoSQL and other SQL databases, each which had their challenges. I like the free tooling that Oracle provides - The APEX app builder and XE database are nice, if not hosted on Oracle Cloud - try the libvirt instance to evade Oracle's cloud grip.
I think the problem here is developer experience and a negative bias toward Oracle, which is understandable and prevalent in the developer community.
Cheers
Shoutout to ThatJeffSmith for contributing to the community!
Without escalation, sometimes tickets bounce between groups.
I filed a ticket on getting sqlplus to work in a chroot, and finally figured it out myself with strace. I didn't think it warranted escalation.
Their sqlplus command-line tool for linux program was written in 1982 it says, and it still doesn't allow for use of the up arrow to retrieve history, and is very quirky with how it handles editing of code. Their Oracle Forms product is very klunky, too. I'm happy I don't need to set up Web Logic servers, because that looks like a nightmare, too.
A little work on tooling would go a long way.
I am partial to using joins in where clauses instead of ansi sql join statements, so I guess there's that.
A java remake called SQLcmd fixes some of the other problems.
Forms? Didn't that go out of support?
> Oracle even prints malicious error messages
From the ancient humorous internet textfile hacktest.text[1] ("THE HACKER TEST - Version 1.0")
0241 Is your job secure?
0242 ... Do you have code to prove it?
Probably a security issue. If you tell a user “table T exists, but you don’t have rights to write to it because you can’t increase sequence S”, you’re leaking the information that a sequence with that name exists.
It’s the same reason a good login system will say “invalid username or password” instead of “invalid password” and its recovery screen “if that’s a valid user name, a mail has been sent to the address associated with it”.
Good to know I’m not the only one doing it this way. Sometimes it’s the only way.
Yes Virginia, Oracle is the Devil.
marcosdumay disagrees:
https://news.ycombinator.com/item?id=28484963
NULL and empty string are often equal, but sometimes not. Life would be boring otherwise.
Plus, Oracle charges per-cpu core used, so we had to license an entire portion of our datacenter...
There is no interface that I can see that takes a temp table or set of parameters.
I've also grown to dislike their (+) syntax. Super convenient but mixing the left joins into the WHERE clauses makes them get lost pretty easily.
I've also spent a ton of time with mystery crashes in large queries in PHP and Java where the crash only seemed to occur if the prepared statements had \r\n instead of \n, or went away on clearing statement cache until next time a set of statements were loaded.
Oh, and the annoying VARCHAR limits - thankfully higher than the 4000 it was at for a long time, but still irritating even now. Trivial example: select listagg(level,',') from dual connect by level <= 8000
A wall of text with options and no actual code example that you can try isn't going to help so much.
Options and parameters everywhere.
I do like PostgreSQL. MSSQL neutral to positive (nice to administer/setup).
Seems like such a weird limitation, and the error messages definitely where not helpful.
But then there's the licensing and the sales guys. Talk about pure evil.
While I certainly have a few issues with postgres, It does the job with minimal bullshit and most of the time, just works. I'm at a point where I just trust it to do its damn job. How often do you hear people complain about postgres or mysql the way you hear people complain about oracle's enterprise db offerrings?
In practice it rounds off to the nearest second if you try to touch it in any way, or even look at it slightly funny. So much so that the microseconds are practically unusable.
Eventually I gave up, and fetched it back into Python and did the arithmetic there before sending it on.
It may have been that there is an obvious answer which I simply failed to find due to the fact that I hadn't used Oracle in a decade. But I went down a number of dead ends before coming up with an approach that worked. (And the person before me had simply not noticed the problem...)