SQL Developer not fetching rows for views

4
votes

I am using SQL Developer Version 4.0.3.16 and JDK Version Java(TM) Platform 1.7.0_71, (OS Windows 7)

When I am executing a query as select * from table_name, the query is fetching rows.

But if I try to fetch the rows from a view select * from view_name the query is fetching 0 results even if the view is pulling records.

This I have confirmed by running the same query in different machine with same db connection.

What could be possibly wrong with settings in my machine. I have tried by completely deleting the \AppData\Roaming\sqldeveloper folder and running the SQL developer again, deleting and creating the new connections.

Any suggestions in this regard will be highly appreciated.

5
sqloraclevieworacle-sqldeveloper
This I have confirmed by running the same query in different machine with same db connection You mean you could see the SQL query result on another client connecting to same database? What do you see in the query result? Does it say no rows returned if you execute it as script? - Lalit Kumar B
Yes, I can see the query results on another client connecting to same database.It fetched rows of the view . In my machine I just get the message that 0 rows fetched.Moreover If I run the query as script it shows 'no rows selected' . - Siddhartha Chauhan
Are you sure the database connection is same and you are not connecting to different schema or a different database altogether? - Lalit Kumar B
Please confirm on SQL*Plus from the same machine you got problem with - Lalit Kumar B
This sounds a lot like a session with uncommitted changes being used to check that the view gives the required data, whereas all other session cannot see the change. - David Aldridge

5 Answers

0
votes

I had a similar issue a few days ago trying to bring up a view. I prefixed the view with the schema. That worked for me. Hope this helps.

select * from schema.view;
0
votes

There may be two possibilities.

  1. you have to check read access is granted for you or not.
  2. Recompile your view and check. Because what happens sometimes we delete some columns from the tables which we are using in the view.it means that now the view is invalid.
0
votes

The NLS setting answer worked for me. I checked the 'skip NLS settings' box, ran the same query again, and this time I got the table rows. Prior to changing the NLS settings, I was getting empty rows when I queried on a certain table in a schema.

0
votes

This is latest Java installed in your PC or laptop. Uninstall latest JDK and install jre-7u65-windows-i586.exe. It should work perfectly fine. I had same issue and

-1
votes

Reset or enable check box your NLS setting at your SQL developer > Preferences > Database > NLS