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 :
TOAD : SELECT USERENV (‘LANG’) FROM dual; => US
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. 3. Change LANGUAGE to AMERICAN. 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 🙂
Cheers
Regards,
Adityanath
Categories: Administration, Uncategorized
Adityanath's Oracle Blog 
Thank you for sharing the solution.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Helpful piece of info. SQL Developer shows weird things at times.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Thanks a ton. You saved my day.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
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.
I love your wordpress template, wherever did you down load it through?
Hello,
I am using Bromley theme available with wordpress.
Regards,
Adi
yay google is my world beater assisted me to find this great web site! .
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.
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.
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