Another reason to despise Oracle
-
I have a coworker out today and it fell to me to run some reporting scripts against one of our Oracle databases. Each and every one of them failed with one of these two errors: ORA-01843: not a valid month ORA-01858: a non-numeric character was found where a numeric was expected These scripts run without issue for them every single day, so I'm like :wtf: After an hour of unsuccessfully searching the interwebs for an answer that worked, it dawned on me to check the registry for local date setting. Bingo, there it was @HKLM\Software\Oracle\Key_OHXXXXXXXXXX\NLS_DATE_FORMAT. Changed it from MM/DD/YYYY to DD/MM/YYYY and all the scripts ran. I'm still pretty green with Oracle databases but nothing in Oracle Support pointed to checking a stupid registry key :mad: I need more :java:
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
This is why they invented ISO 8601. :^) Can't we all just use
YYYY-MM-DD? -
I think that I must have inherited my work PC from a previous user without being wiped first and thusly inherited their settings. :omg:
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
Oracle should not have the setting in the first place; there is a system setting. Especially for the developer of a database-server, I'd expect that they'd at least know how idiotic it would be if you had to keep each setting per application for each workstation.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
This is why they invented ISO 8601. :^) Can't we all just use
YYYY-MM-DD? -
Oracle should not have the setting in the first place; there is a system setting. Especially for the developer of a database-server, I'd expect that they'd at least know how idiotic it would be if you had to keep each setting per application for each workstation.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Eddy Vluggen wrote:
Oracle should not have the setting in the first place; there is a system setting.
I'm not arguing with that point. From a database development perspective, the only thing a user should have to be concerned with is 'Do I have the right connection string?' and 'Do I have the necessary permissions?'
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
-
I have a coworker out today and it fell to me to run some reporting scripts against one of our Oracle databases. Each and every one of them failed with one of these two errors: ORA-01843: not a valid month ORA-01858: a non-numeric character was found where a numeric was expected These scripts run without issue for them every single day, so I'm like :wtf: After an hour of unsuccessfully searching the interwebs for an answer that worked, it dawned on me to check the registry for local date setting. Bingo, there it was @HKLM\Software\Oracle\Key_OHXXXXXXXXXX\NLS_DATE_FORMAT. Changed it from MM/DD/YYYY to DD/MM/YYYY and all the scripts ran. I'm still pretty green with Oracle databases but nothing in Oracle Support pointed to checking a stupid registry key :mad: I need more :java:
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
Hardly sporting to blame the database because somebody, at some point, made the dumb-assed decision to allow for implicit data conversion - passing a string literal and assuming that nobody would ever change environment settings. Always, always, always, always use an explicit format mask for data conversions from string to date. Did I mention "always"? It's not hard. you don't: WHERE yourDatefield = :your_parameter --fingers crossed and hope it works! Instead: WHERE yourDatefield = TO_DATE(:your_parameter, 'YYYY/MM/DD') -- or whatever your string is formatted as.
-
Hardly sporting to blame the database because somebody, at some point, made the dumb-assed decision to allow for implicit data conversion - passing a string literal and assuming that nobody would ever change environment settings. Always, always, always, always use an explicit format mask for data conversions from string to date. Did I mention "always"? It's not hard. you don't: WHERE yourDatefield = :your_parameter --fingers crossed and hope it works! Instead: WHERE yourDatefield = TO_DATE(:your_parameter, 'YYYY/MM/DD') -- or whatever your string is formatted as.
I wasn't really blaming the database. It does what it is supposed to do, store data is a consistent and reliable format. I was more peeved at Oracle for not having any readily accessible documentation to help find some obscure registry key that causes string date conversions to fail just because they were out of an explicitly defined order. Also, I totally agree that string literals for dates is never a good idea. I didn't write these scripts and they execute against production data so I wasn't about to go rewriting them on a whim just yet. I'll save that one for a later date.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
-
I wasn't really blaming the database. It does what it is supposed to do, store data is a consistent and reliable format. I was more peeved at Oracle for not having any readily accessible documentation to help find some obscure registry key that causes string date conversions to fail just because they were out of an explicitly defined order. Also, I totally agree that string literals for dates is never a good idea. I didn't write these scripts and they execute against production data so I wasn't about to go rewriting them on a whim just yet. I'll save that one for a later date.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
It's not the literals that are the problem - they have their uses. It's assuming that the database will format them the way you want that is the problem. StackOverflow is chock-full-of-examples of people hitting that same problem. Fortunately its an easy thing to not do, and once learned most people are pretty good about remembering. Other than that, lots of perfectly good reasons to hate Oracle. It's been both my nemesis and paycheque for two decades now...lol.
-
It's not the literals that are the problem - they have their uses. It's assuming that the database will format them the way you want that is the problem. StackOverflow is chock-full-of-examples of people hitting that same problem. Fortunately its an easy thing to not do, and once learned most people are pretty good about remembering. Other than that, lots of perfectly good reasons to hate Oracle. It's been both my nemesis and paycheque for two decades now...lol.
What gets me is that I pulled up the registry settings on a pc that is known to successfully run the scripts and the offending key wasn't even there. Their system just defaulted to the server settings but because some previous owner of my work pc had explicitly set the date format in the registry, the scripts failed for me. Too many settings in too many locations makes for too many places for s**t to go wrong.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
-
Probably for the same reasons you can't get Americans to use the metric system.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
Americans? I still can't think in litres, kilos, and kilometres, and I've been living in continental Europe for the past fifteen years! Can't blame 'em for not using the metric system when everyone around them is not using it, too.
I wanna be a eunuchs developer! Pass me a bread knife!
-
What gets me is that I pulled up the registry settings on a pc that is known to successfully run the scripts and the offending key wasn't even there. Their system just defaulted to the server settings but because some previous owner of my work pc had explicitly set the date format in the registry, the scripts failed for me. Too many settings in too many locations makes for too many places for s**t to go wrong.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
Which is why counting on implicit format mask settings is not recommended! None of those settings matter for anything other than display when you code with explicit conversions where you control the format mask. Otherwise, as you have discovered, you're walking an unknown dependency path waiting for someone else to set the default format for you! Code with TO_DATE(string, mask) and the problem goes away - permenently!
-
Probably for the same reasons you can't get Americans to use the metric system.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
The metric system is used in much of America; it's only one largely insignificant portion of North America where you'll find the most hold-outs.
-
I wasn't really blaming the database. It does what it is supposed to do, store data is a consistent and reliable format. I was more peeved at Oracle for not having any readily accessible documentation to help find some obscure registry key that causes string date conversions to fail just because they were out of an explicitly defined order. Also, I totally agree that string literals for dates is never a good idea. I didn't write these scripts and they execute against production data so I wasn't about to go rewriting them on a whim just yet. I'll save that one for a later date.
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016
Foothill wrote:
I was more peeved at Oracle for not having any readily accessible documentation to help find some obscure registry key that causes string date conversions to fail just because they were out of an explicitly defined order.
It's Chapter 15[^] in the Database Platform Guide.
Wrong is evil and must be defeated. - Jeff Ello
-
Foothill wrote:
I was more peeved at Oracle for not having any readily accessible documentation to help find some obscure registry key that causes string date conversions to fail just because they were out of an explicitly defined order.
It's Chapter 15[^] in the Database Platform Guide.
Wrong is evil and must be defeated. - Jeff Ello
-
I have a coworker out today and it fell to me to run some reporting scripts against one of our Oracle databases. Each and every one of them failed with one of these two errors: ORA-01843: not a valid month ORA-01858: a non-numeric character was found where a numeric was expected These scripts run without issue for them every single day, so I'm like :wtf: After an hour of unsuccessfully searching the interwebs for an answer that worked, it dawned on me to check the registry for local date setting. Bingo, there it was @HKLM\Software\Oracle\Key_OHXXXXXXXXXX\NLS_DATE_FORMAT. Changed it from MM/DD/YYYY to DD/MM/YYYY and all the scripts ran. I'm still pretty green with Oracle databases but nothing in Oracle Support pointed to checking a stupid registry key :mad: I need more :java:
if (Object.DividedByZero == true) { Universe.Implode(); } Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016