we are testing metabase and we have problems with our oracle database.
This statement for example select to_number('100,12') from dual returns ORA-01722: invalid number. If I execute the same statement over the PLSQL devoloper via windows there are no problems.
This is how metabase is started on our debian (Linux 8, 64bit) server: java -Duser.language=de -Duser.country=DE -Duser.region=DE -Duser.variant=DE -Duser.timezone=Europe/Berlin -jar metabase.jar
The REPORT TIMEZONE in the settings is set to âEurope/Berlinâ.
We are using the JDBC Driver JAR and the latest Metabase version!
The log at startup shows âSystem timezone is âEurope/Berlinâ âŚâ.
Somebody help me, please! Please ask me if you need more information.
So if I understand correctly Oracle is basically complaining about that itâs does not agree that comma is the decimal separator?
From goggling maybe here are hints towards an initial workaround (note NLS_NUMERIC_CHARACTERS):
Itâs been a while since I worked with Oracle, but I remember to have had struggles also from Windows clients connecting via JDBC where we had to set NLS_LANG environment variables - a google for https://www.google.dk/search?q=java+sql+client+nls seems to give a lot of Oracle hits.
I donât think the problem is specific to Metabase, but that it would fail in the same way connecting from any Java/JDBC connection on that specific account/environment settings on that Debian machine. So thatâs another thing you can try to isolate the problem.
The to_number function has a third parameter to set the regional settings. If I were using a lot of EU (excluding UK) data, Iâd set it on every use to remove the reliance on the server settings.
I think too, that this is a Java/JDBC Problem and not specific to metabase. But thatâs the only way how I can replicate the error.
When I call a Oracle function inside a Metabase question with this statements execute immediate 'alter session set nls_language = ''GERMAN'' '; execute immediate 'alter session set nls_numeric_characters = '',.'' ';
then afterwards the statement select to_number('100,12') from dual doesnât return an error anymore for this session. When I open a new question then there is the error again.
I already googled a lot, and my understandig is that the Oracle JDBC Thin Client which is used by metabase doesnât use the NLS variables. The thin client generates the location infos like the decimal sepparator from the java settings, which can be set when I start the jar file with parameters like this: java -Duser.language=de -Duser.country=DE -Duser.region=DE -Duser.variant=DE -Duser.timezone=Europe/Berlin -jar metabase.jar
But nothing seems to work. Nevertheless I export the NLS environments before starting the jar file:
Unfortunately, that is not an option, because the to_number function is used in an custom oracle function inside of the database. I canât change the functions.