Oracle/PostgreSQL: NLS / Sort

[pavo123]

Hi Isomorphic,

is it possible to set up in server.propertis default sorting of Oracle database?



The same question for PostgreSQL database:
Is it possible to set up in server.propertis default sorting of PostgreSQL database?

Best regards
Pavo

[Isomorphic]

See docs for SQL settings - if it’s a setting that is allowed in a JDBC URL, you could do that it way. Otherwise if it’s a setting of the JDBC driver class, setting things in the SQL settings for the DB means we will attempt to call the appropriate setter with the specified property.

[Blama]

Hi Isomorphic,

you are referring to these docs, thanks, I did not know/remember them.
We also figured out you might use reflection as you say in the docs, as the applied settings match closely the setters in the Oracle JDBC docs.

Unfortunately, there I don't see a way to configure this. Stack Overflow shows a not nice way (IMHO) of doing this using Locale.setDefault().

In SQL Developer you can see that if you change the NLS Params, it sends an "ALTER SESSION SET ...".
Such code would of course only need to be executed once, so I wonder if it is possible to execute it from Apache Commons DBCP when it creates a connection, as I assume this is the correct place to do this.
It seems there is (also in currently used v1.4), as you can see here in the Apache docs.

As the setter accepts a list, I do wonder now how you set this in server.properties?

Thank you & Best regards
Blama

[Isomorphic]

From the StackOverflow thread, people mention ways of setting NLS properties on the Oracle instance. We would recommend that approach.

If you are somehow unable to do that, while there's no way to get involved in connection creation, you can get access to the Connection object and execute your own JDBC operations, as shown in this sample. This would allow you to execute your ALTER SESSION at the beginning of any DataSource operation, and you could store hashcodes of Connection objects to make sure you didn't unnecessarily do it twice.

But obviously just setting the property on the Oracle instance is better.