Why is the request executed under Windows, and under Linux error: "ORA-01722: invalid number"?
There is a Python / SQL script that is written under Windows.
Everything is elementary simple, I execute the request and do fetchall().
The problem starts when I try to run this same script on a Linux server. The request is executed exactly the same time as under Windows, but when executing fetchall() it gives an error:
Cx_Oracle.DatabaseError: ORA-01722: invalid number
The query is just select, and the final sample is about 3000 rows.
Ustanvlivayu connection:
connection = cx_Oracle.connect(user_name, password, dsn_tns,encoding='UTF-8',nencoding='UTF-8')
cursor = connection.cursor()
cursor.execute(_SQL)
Under Windows-everything is fine, and under Linux-an error
Which way to look? What could be the problem?
1 answers
It's very simple. The request contains an implicit data type conversion.
For example, dates or numbers are converted to a string value (or vice versa). Since the national settings on Windows and Linux may differ, executing the same query under different operating systems can lead, as in the question, to errors, or, much worse, to different query results.
Solution (the first is preferable to):
- Change the request so that it contains only explicit data type conversions.
- The environment variable
NLS_LANGsets the same national session settings on both systems.
The simplest example of what can lead to an error:
select 0 + '1,23' res from dual;
RES
----------
1,23
And now:
alter session set nls_territory='America'
/
select 0 + '1,23' res from dual;
ORA-01722: invalid number