SQL Developer returns 0 rows

 

SYMPTOMS

When running a query [SELECT count(*) from <table_name>] on a table in SQL Developer, a count of 0 is returned.

If the same SELECT is run as the same user in SQL*Plus, then a positive row count is returned.

CAUSE

The query is dependent on the results of USERENV('LANG').

This will give different results for SQL Developer and SQL*Plus.

In order for USERENV('LANG') - and the query using this - to give the same results, the LANGUAGE part of NLS_LANG needs to be modified.

SOLUTION

Change the LANGUAGE part of NLS_LANG such that USERENV('LANG') returns the same for SQL Developer and SQL*Plus.

Example:

Tools->Preferences->database ->NLS Parameters from "English" to American"
set NLS_LANG=AMERICAN_AMERICA.UTF8 gives:
SELECT USERENV ('LANG') FROM dual; => US
And
set NLS_LANG=DUTCH_AMERICA.UTF8 gives:
SELECT USERENV ('LANG') FROM dual; => NL