0

Issue:

My java job runs fine in my development environment, but when I deploy it on the integration server, I get a issue. This makes me think there is a configuration difference between my dev. env. and the integration server.

Context:

I am not a java or spring expert. This is rather an issue.

I call a MY.PROCEDURE piece of PL/SQL code, that in turn calls an Oracle view.

The below instruction in the Oracle view works fine in my dev. env., but on the integration server it is causing the issue:

T1.DATE between '01.07.2015' and  '07.07.2020'

Giving an ugly

Caused by: java.sql.SQLDataException: ORA-01843: not a valid month
ORA-06512: at "MY.PROCEDURE", line 36

When I specify the date format with a TO_DATE, it vanishes. I know this is good practice, and should obviously be mandatory for our team to do. Anyway I would like to understand this configuration discrepancy we have between Dev and Int.

Help

Any idea where I should configure the locale when I deploy this thing?

I wish I could setup the local (or date format) locally, without modifying the java deliverable. Is it possible ?

J. Chomel
  • 8,193
  • 15
  • 41
  • 69

2 Answers2

1

It appears as though your NLS_DATE_FORMAT is different in your environments. Run the following in each environment:

select value
from   nls_session_parameters
where  parameter = 'NLS_DATE_FORMAT';

This is why you should always specify the format and not rely on implicit conversion. If necessary you may be able to run the following:

alter session set nls_data_format = 'mm.dd.yyyy';

or perhaps that should be 'dd.mm.yyyy'. That cannot be determined from the given information.

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Hi @Belayer, I asked an integration question=>"without modifying the java deliverable". My question is how do I configure this "alter session" locally on the environments. Is it at driver level? Listener? Client? and how. – J. Chomel Aug 17 '20 at 05:37
  • ""without modifying the java deliverable" my guess is you are NOT. It is an SQL statement, you will have to run it as such. It can be a single call directly after connect. An alternative would be getting your DBA to reconfigure the database. I'm guessing that is not going to happen and if it does it will be the dev environment; which will necessitate wholesale code changes. In the long run best option write your code so that it does not depend on nls setting. Actually specify the format in the [to_date](https://www.postgresqltutorial.com/postgresql-to_date/) (to_char) statement. – Belayer Aug 17 '20 at 17:45
  • I don't think you got this one. Sorry, but thank you for trying. – J. Chomel Aug 18 '20 at 06:07
1

The environment difference between development and the integration server is the first is a Swiss(FR) setup OS. And... the server is a Linux with the default English's.

So you start to see where I have to go.

Tell Java you run your thing with 'fr' locale:

I had to complete the java command line with the below:

 -Duser.country=fr -Duser.language=fr

Here is where I found inspiration: NLS_LANG setting for JDBC thin driver? (most upvoted answer).

J. Chomel
  • 8,193
  • 15
  • 41
  • 69