Oracle SQL Is The Devil

Oracle SQL Is The Devil

Generally speaking, I like working with oracle, but sometimes it makes me crawl up the walls.

Over the past months I have fought battles of epic proportions against Oracle databases, and wasted countless hours on errors that were fully preventable, and should have been five-minute fixes instead of day-long ordeals.

Oracle hates giving error details

Anyone who has ever worked with Oracle SQL knows the type of error messages that Oracle gives you. Things like „Invalid SQL statement“ or „Missing Right Paranthesis“ tell you pretty much nothing, especially in the cases where they do not come with line numbers (or „row 1, column 7“, which is the precise point where the function call begins.)

In my mind Oracle is this guy who has had this job for a decade now and just can’t be bothered to actually do it, giving you the least amount of effort that doesn’t get him fired.

Oracle is the equivalent of the guy who tells you to „just google it“ when you come asking for help.

Oracle even prints malicious error messages

Imagine this: You have a function in your code that is supposed to insert entries into a database table. Simple enough, right? Sure, the table is new, on a different database schema, there are likely some grants missing or other quirks, but those are easy to iron out.

As expected, we get the well-known „table or view not found“ error, which in itself is a pretty weak error message for what is highly likely to be „hey, I’m missing a GRANT here“. If I had been tasked to write this I would have called this „access denied“, but eh, whatever.

But how in the world is it okay for Oracle to throw me for a loop as I try to pinpoint why the existing grants aren’t sufficient, debugging the whole program to see which user credentials are used – only to find out that it was neither a table nor a view, but rather the SEQUENCE used to increment the ID column that needed grants for the user?

This error message is actually worse than giving a non-descriptive error, because it turned me on a completely different path by the way the message is worded.

And here is another example: „Invalid number of arguments“ as you try to execute a stored procedure from within the code. This may just be what it says on the tin, but I also learned that this can mean that you passed an output variable without declaring a specified length or with an insufficiently high number. Since I was calling it from within a piece of C# code everything looked just right to me with the OracleDBType.Varchar2, when really I should have written OracleDBType.Varchar2(100).

The same user error can also result in „no function with this signature found“ or „invalid cast exception“, depending on how Oracle is feeling that day.

Oracle doesn’t know what oracle is doing

Have you ever been inside a hardware or electronics store and asked the guy in the red jacket, only to hear „Sorry, I don’t work here“? Well, that is how Oracle reacts each time you ask it a question. And much like any hardware store, you will get the most valuable bit of information from an old guy who doesn’t even work for Oracle, but somehow still comes in every day.

Related  I Learned Coding Mostly From Porn
http://www.dba-oracle.com/t_ora_06502_pl_sql_numeric_value_error.htm

If this friendly looking old man has never helped you (or ordered you buried alive by the look of it) then you have just not worked with Oracle for long enough.

But good luck getting any kind of usable information out of the main Oracle forums, it’s like going to a Quora page and expecting an answer to your original question. Sure, you learn a lot about a whole lot of things, but the solution to your actual problem sits deep inside the comments of a StackOverflow answer that was labeled as duplicate, with an OP that was heavily downvoted for some reason and an accepted answer that doesn’t even answer the question.

Oracle is not consistent

Take a look at this line of code, what do you see?

EXECUTE IMMEDIATE 'ORDER_66(' || parameter || ');';

Apart from looking ugly and being a possible source of SQL injection, this looks just about right, right? Wrong, this does not work. Why though, when I copy that statement to the editor and run it there, all works beautifully.

But, of course, as any traumatized developer will tell you immediately: The semicolon at the end of that script is the problem. Execute Immediate does not work when you put it there, because why would it work the same way in two different places?

Log files can kill your script

I kept the best for last, the error that made me sit down and write this post in the first place.

Imagine you are working on a script that by all means should work ™ but doesn’t. You lose hope for humanity’s continued existence, and start adding log outputs after each and every function call to finally figure out where the script stops working (because of course Oracle doesn’t just tell you).

You find a bug here, a bug there, and start dreaming of a better tomorrow, when suddenly the script does a full loop without errors. It does another, and you watch the beautiful sight of seeing the counter variable increase.

Then, suddenly, inexplicably, it stops after iteration 459 out of roughly 900. This makes no sense, if this script fails at this point it would have already failed at the first iteration.

It makes even less sense considering that the log outputs simply stopped instead of printing out my error messages that I placed in each and every try-catch block.

Well, the long story short is that Oracle is so adverse to giving users logging output that the logging statement itself can get a script to fail, when the internal buffer for the logging output reaches its overflow. So be aware, the problem line that cost me hours to debug was simply this:

dbms_output.put_line('error at function_name in line 56');

And that, my dear readers, is why Oracle SQL is pure, unfiltered evil.

Related

Comments

  • MJ MJ September 10, at 11:43

    Fun take, and I agree with your stance.
    The redhead jab, however, is completely unnecessary…

    Reply
    • freedomadmin freedomadmin September 10, at 16:43

      A bunch of people commented that, I thought it was funny enough to keep. But I reworded the intro, it’s no use pissing people off for no reasons, I see how one might take offence at it. Hope it didn’t detract too much from the article.

      Reply
  • Oliver Oliver September 10, at 16:39

    All so true. Oracle Database is the least friendly database out there…when it works, it works great..when it doesn’t it fails miserably.

    God forbid you’re one of the poor souls that has to use OCI (Oracle Call Interface) and need to get help where every search result for OCI is related to Oracle Cloud Infrastructure (also OCI, thanx Oracle).

    Reply
    • freedomadmin freedomadmin September 10, at 16:42

      Oh, that sounds horrible. I have the same each time I work in one of the programming languages with „funny“ names. No, I did not want to debug jewelry, but thanks for all those gemstones and rubies haha.

      Reply
  • Bikram Dhaliwal Bikram Dhaliwal September 10, at 21:04

    How about ‚Looping chain of synonyms‘ instead of table does not exist?

    Reply
  • chx chx September 11, at 08:43

    https://news.ycombinator.com/item?id=18442941 describes the hell that is working on the Oracle database.

    Reply

Post Reply