https://codingtofreedom.com/oracle-sql-is-the-devil/ Coding To Freedom [ ] Oracle SQL Is The Devil C#, General Programming Topics, Oracle Oracle SQL Is The Devil Posted on September 10, 2021 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 Critical Lessons From Fixing Bugs In Production For Years [image]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 (tm) 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 I Learned Coding Mostly From Porn I Learned Coding Mostly From Porn Selenium Is Not A Sedative Selenium Is Not A Sedative How I Have Made Side Income As A Programmer How I Have Made Side Income As A Programmer 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 Post Reply Hier klicken, um das Antworten abzubrechen. [ ] [ ] [ ] [ ] Comment *[ ] Your name [ ] E-mail [ ] Website[ ] [ ]Meinen Namen, E-Mail und Website in diesem Browser speichern, bis ich wieder kommentiere. [Post Comment] About About I'm your friendly neighbourhood code monkey who writes about my own journey towards moderate financial freedom through coding. If you like you can come along for the journey and see where it leads me as I grind my teeth on paying for my rent, food and bills with side income. [Get-My-Bookmarks] Popular Posts Oracle SQL Is The Devil Oracle SQL Is The Devil September 10, 2021 4 How To Thrive As An Average Programmer How To Thrive As An Average Programmer August 17, 2021 0 What Programmers Can Learn From Sherlock Holmes What Programmers Can Learn From Sherlock Holmes August 17, 2021 0 losingmyconnection Unknown heights. ---- #urbex #lostplaces #lostpla Unknown heights. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill Stop stairing at me. ---- #urbex #lostplaces #los Stop stairing at me. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill The seatuation room. ---- #urbex #lostplaces #lost The seatuation room.----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill Sooner or ladder. ---- #urbex #lostplaces #lostpl Sooner or ladder. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill #climbing Eimer mit Profis arbeiten. ---- #urbex #lostplace Eimer mit Profis arbeiten. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill Some days I'm just Tyred. ---- #urbex #lostplaces Some days I'm just Tyred. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill High heels and low morals. ---- #urbex #lostplace High heels and low morals. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill This is how artists are born. ---- #urbex #lostpl This is how artists are born. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill #graffiti Let's just float on the surface of life and see wh Let's just float on the surface of life and see where the waves may carry us. ----# urbex #lostplaces #lostplacesgermany #rurex #decay #urbanexploration #urbanexploring #abandonedplaces #abandoned #urbexphotography # urbexgermany #verlasseneorte #urbexphoto #urbexpeople #urbex_utopia # forgottenplaces #ig_urbex #savethedecay #decay_explorers # urbex_kreisklasse #urbexandchill #graffiti Only the weak of heart are afraid of asking the bo Only the weak of heart are afraid of asking the bolt questions. ----#urbex #lostplaces #lostplacesgermany #rurex #decay #urbanexploration #urbanexploring # abandonedplaces #abandoned #urbexphotography #urbexgermany # verlasseneorte #urbexphoto #urbexpeople #urbex_utopia # forgottenplaces #ig_urbex #savethedecay #decay_explorers # urbex_kreisklasse #urbexandchill You look like you had a roof night. ---- #urbex # You look like you had a roof night. ----#urbex #lostplaces #lostplacesgermany #rurex # decay #urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill Barred for life. ---- #urbex #lostplaces #lostplac Barred for life.----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill Hell hath no fury like a window thorned. ---- #urb Hell hath no fury like a window thorned.----#urbex #lostplaces #lostplacesgermany # rurex #decay #urbanexploration #urbanexploring #abandonedplaces # abandoned #urbexphotography #urbexgermany #verlasseneorte #urbexphoto #urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill There are those who can and those who can't. ---- There are those who can and those who can't. ----#urbex #lostplaces #lostplacesgermany # rurex #decay #urbanexploration #urbanexploring #abandonedplaces # abandoned #urbexphotography #urbexgermany #verlasseneorte #urbexphoto #urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill Roam, sweet roam. ---- #urbex #lostplaces #lostpl Roam, sweet roam. ----#urbex #lostplaces #lostplacesgermany #rurex #decay # urbanexploration #urbanexploring #abandonedplaces #abandoned # urbexphotography #urbexgermany #verlasseneorte #urbexphoto # urbexpeople #urbex_utopia #forgottenplaces #ig_urbex #savethedecay # decay_explorers #urbex_kreisklasse #urbexandchill This is right where I Ballon. This is right where I Ballon. Steel built both the foundation of mankind and the Steel built both the foundation of mankind and the downfall of civilization. ----# urbex #lostplaces #lostplacesgermany #rurex #decay #urbanexploration #urbanexploring #abandonedplaces #abandoned #urbexphotography # urbexgermany #verlasseneorte #urbexphoto #urbexpeople #urbex_utopia # forgottenplaces #ig_urbex #savethedecay #decay_explorers # urbex_kreisklasse #urbexandchill Lock me in your arms and tell me it will all be al Lock me in your arms and tell me it will all be alright. ---#urbex #lostplaces # lostplacesgermany #rurex #decay #urbanexploration #urbanexploring # abandonedplaces #abandoned #urbexphotography #urbexgermany # verlasseneorte #urbexphoto #urbexpeople #urbex_utopia # forgottenplaces #ig_urbex #savethedecay #decay_explorers # urbex_kreisklasse #urbexandchill The brutalist architecture commanded the otherwise The brutalist architecture commanded the otherwise beautiful landscape, ensuring that no one could ever forget who truly reigned in these lands. ----# urbex #lostplaces #lostplacesgermany #rurex #decay #urbanexploration #urbanexploring #abandonedplaces #abandoned #urbexphotography # urbexgermany #verlasseneorte #urbexphoto #urbexpeople #urbex_utopia # forgottenplaces #ig_urbex #savethedecay #decay_explorers # urbex_kreisklasse #urbexandchill Our livelihoods burned, and with them the sins of Our livelihoods burned, and with them the sins of our past. We became free the moment we lost everything we had fought to achieve, to acquire, and the moment we stopped possessing things was when we got a hold of ourselves. Load more Follow me on insta Twitter Instagram Facebook LinkedIn Google+ (c) 2015 Copyright Firenze theme. All Rights reserved. Different Themes Follow my journey and stay up to date on my efforts! [ ] [ ] [Yes, I want a free list of writing resources that make money!]