Oracle APEX vs SQL Commands/database execution - default conversion
Getting different results?
Another one for the books. You have a procedure or a function that someone developed which runs in APEX application without any problems.
Someone gives you to change a smallest piece in it and you are ready to test it. You take the same code and try to run it in SQL commands or in your SQL developer.
All of the sudden some strange database errors come up like typically ORA-01843 not a valid month.
You make sure parameters you pass in are correct? Check.
You are sure all other things are set correctly as in your APEX app? Check.
One in APEX runs fine where this one will not and you are losing your patience/time finding why this error is now occurring.
This is a beauty of implicit conversions which I am sure you heard earlier about. Meaning if you do not follow standards across all of your code, default conversions might be happening between these different environments that you are not expecting and that your code is not handling well but you just didn't notice it.
What do I mean? Well if you run your code in APEX and it works there might be some default date format settings that differ from your local database or SQL commands settings. As soon as this is the case there might be issues in your code if implicit conversions are being used over providing explicit formatting especially when dates are involved.
Okay but how do we confirm this theory? Easiest possible way would be create a region in APEX and check what it does in this situation
You will get a result in a page like 10-JAN-24 in my example.
Which means your APEX NLS_DATE_FORMAT could be "DD-MON-RR".
This is typically set on application globalization settings. If left empty they might be defaulting to this.
Cool lets see what SQL commands now says about the same thing.
In my case I got 01/10/2024 which indicates MM/DD/YYYY as a default format. What do you think will happen when your data do not match one of the two formats - clearly you would get an error.
Of course in most cases you would look deep under into where exact problem is and apply appropriate formats to align both sides and problem would go away.
Now it may seem obvious but on the other hand you can spend hours debugging and looking for why it works in one where it doesn't work in the other.
It is important to remember these settings can differ and cause you issues. Like it was in my case. Even though it is clear to everyone why it happens it might not ring a bell in the head at the right moment.
Problem also was that I wasn't able to change/fix root cause of this error and that all that was needed was to make thing tested and give back to the team.
So how do we make it work to force required date format on SQL commands side too. Before the call to your procedure in your SQL commands session you can execute
and after this it will run without errors; exactly as it does in your APEX session.
Hope it saves your time; Thank you Garry.
Happy APEXing,
Lino
