In SQL Developer you can configure how you want things to work for your session. How DATES should appear. If numbers should use a ‘.’ or a ‘,’ to indicate group separators and decimal points.

We try to guess these things based on your local machine settings, but you can of course override them.

Basically this means, we do ‘nothing.’

But let’s see what happens when you DO set something.

I’m going to change a whole bunch of stuff, stuff that’s dramatically different from what’s going on with my machine and even the database configuration.

Upon opening the connection, we can see SQL Developer telling the database how we’ve configured our session for these items.

OK, that feels a little icky, but no worries I still realize vegemite is an abomination.

The Statements Panel

One of my favorite meta features in SQL Developer, the Statements panel shows everything we send across the JDBC driver for thin connections to the database. So you can change things in the preference, clear the panel, ‘do stuff’, and check the panel again to see what’s changed.

Like, toggling the ‘Skip’ option for NLS, and seeing that we’re not setting stuff anymore, even for NLS_DATE and TIMESTAMP_FORMAT.

No NLS settings are being configured for the session now, you’re just going to get what the DB gives you.

And we DO get something for NLS_SESSION for our session(s).

I’m ok with what you give me, I’m an easy going fella.

How does the DB Pick Stuff?

In general the DB doesn’t pick anything. You are telling the DB about your client side environment. There are a few ways. You can tell the DB explicitly in an alter session or by adding it to your SQL statement itself, you can setup your machine with environment variables and registry entries (SQL Developer is a java app and we don’t use those like SQL*Plus does), or you can live with what the DB has setup.

Since SQL Developer is a Java app and of course uses a JDBC driver, the driver itself will detect local setups and config certain NLS properties – even if SQL Developer itself does not – for example The language and territory settings, by default, are set to the Java VM locale settings.

Click on the pic to get to the 12cR2 database docs on this topic.

#2 is what the preference screen for NLS is the bucket that SQL Developer is ultimately falling into.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Jim Cruickshank Reply

    Hi Jeff,

    In SQL Developer 17.4 there appear to be a few more subcategories on #2 depending on your Java settings. THis is very confusing for me.

    Jim

    • Like what?

      Did you see the last paragraph, it sounds like you’re observing this

      Since SQL Developer is a Java app and of course uses a JDBC driver, the driver itself will detect local setups and config certain NLS properties – even if SQL Developer itself does not – for example The language and territory settings, by default, are set to the Java VM locale settings.

Write A Comment