Oracle ORA-01722: invalid number

Hello,

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.

Best regards!

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.

Hope that can lead you in the right direction.

1 Like

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.

1 Like

Hey jornh,

you are correct, that’s the problem.

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:

export MB_PASSWORD_COMPLEXITY=weak
export MB_PASSWORD_LENGTH=3
export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=USER
export MB_DB_PASS=PASS
export MB_DB_HOST=localhost
export MB_PLUGINS_DIR=/etc/metabase/plugins/
export JAVA_TIMEZONE=Europe/Berlin
export LANG=de_DE.UTF-8
export NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
export NLS_NUMERIC_CHARACTERS=,.

You may have already noticed that I’m not an expert. Do you have another idea how to solve the Java/JDBC problem? Thank you.

Thank you!

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.

Sorry, I’m also not an Oracle expert. That NLS experience of mine is almost 10 years old and was very frustrated with it back then as well.

You’re welcome, best of luck!

Are you able to create a view that could do the conversion? It might hit performance,but would work.

2 Likes

We were able to make the conversion in the database, so it’s working right now. Thank you!