oracledb invalid number error

Question

I am using oracledb (2.3.0) with node (v8.11.3). I trying to run the following query, that return results in sqlDeveloper

select TO_NUMBER(RESULTS) from V_RESULTS where RESULTS>=8 AND RESULTS<=10 AND LAND='AT'

When I run it with oracledb:

oracledb.getConnection(
        config.db_connection,
        function (err, connection) {
            if (err) {
                return;
            }

            connection.execute(query,
                function (err, result) {
                    if (err) {
                        console.error(err.message);
                        return;
                    }

                   console.info(results);
                });

        })

I get the following error : ORA-01722: invalid number

I am unable to find the source of the error.

Answer 1

There are several possible causes. You may just have strings which do not represent numbers - i.e. that simply cannot be converted to numbers. SQL Developer might be giving you a false sense of security if you're only seeing the first page of results and hasn't reached one that cannot be converted yet (possibly even with the filter you are using).

Given that discrepancy in client behaviour, though, it's also likely that you have non-integer values and it's confused about the decimal, with the NLS setting being used by SQL Developer differing from the one Node is picking up from your locale. You can demonstrate the same issue by adjusting that value:

alter session set nls_numeric_characters = '.,';

with t (n) as (
            select '1' from dual
  union all select '1.5' from dual
)
select n, to_number(n)
from t;

N   TO_NUMBER(N)
--- ------------
1              1
1.5          1.5

alter session set nls_numeric_characters = ',.';

with t (n) as (
            select '1' from dual
  union all select '1.5' from dual
)
select n, to_number(n)
from t;

Error report -
ORA-01722: invalid number

If that is what is happening you can change your locale or explicitly set your NLS environment up, or you can specify which decimal separator to use by supplying the format model:

with t (n) as (
            select '1' from dual
  union all select '1.5' from dual
)
select n, to_number(n, '999.999')
from t;

N   TO_NUMBER(N,'999.999')
--- ----------------------
1                        1
1.5                    1,5                   

which requires some knowledge of the number of leading and trailing digits to allow.

You can also override NLS_NUMERIC_CHARACTERS as part of the to_number() call, but only if you're supply a format anyway - so that just lets you use D instead of a period or comma, which isn't terribly useful here.

According to this, you can just override it at session level anyway, something like:

        connection.execute(
            "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'",
            function (err, result) {
                if (err) {
                    console.error(err.message);
                    return;
                }

               console.info(results);
            });