SQL Developer providing incorrect query results than TOAD/sql*plus

Some days before I came across very weird issue with SQL Developer. One of the developer in my company was complaining that, she is getting “no rows selected” after executing some select query. According to her it was an unexpected behaviour & expecting some rows from that query.

After having some chat got to know that she was using SQL Developer as tool for running that query.

I tried executing same query by logging with her credentials on my PC but through TOAD (not SQL developer). Unexpectdely I got 300 rows with same query.

Means When I run the one query directly on SQLPLUS or TOAD then it displays complete results, while I run the same query with SQL Developer then it does not provide me any result. 😦

After searching on metalink I found : SQL Developer Returns Zero Rows on Table while SQL*Plus Returns Positive Count (Doc ID 415920.1) 

Which states this issue happens when the query is dependent on the results of USERENV(‘LANG’).

I checked USERENV(‘LANG’) settings in both the tools & found that :

SQL Developer : SELECT USERENV (‘LANG’) FROM dual; => GB

After following below steps for changing LANG settings on sql deveoper issue resolved 🙂

1. Start sqldeveloper.
2. Go to Tools-->preferences-->database-->NLS parameters.
4. Restart sql developer.

For putting some light on this behaviour, Enviornment in our case was Oracle eBusiness Suite which has lots of language specific views (only returns records where userenv(‘LANG’) matches an installed language in eBusiness).

Hope so u will find this post very useful 🙂




6 replies

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s