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 🙂




13 replies

  1. After examine just a few of the blog posts in your website now, and I truly like your method of blogging. I bookmarked it to my bookmark web site list and will likely be checking again soon. Pls take a look at my site as nicely and let me know what you think.

  2. Valuable information. Fortunate me I found your website by chance, and I am shocked why this accident did not happened in advance!
    I bookmarked it.

  3. I really like what you guys tend to be up too. Such clever work and coverage! Keep up the amazing works guys I’ve included you guys to blogroll.

  4. Hey! Would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would really enjoy your content. Please let me know. Cheers

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 )

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