This content has been marked as final. Show 14 replies
If you are logging on as the same user in the same DB and getting different records in one tool (sqlplus) and not in another (SQL Developer / TOAD), you are probably querying a session specific view or VPD table. For example, Oracle eBusiness Suite has lots of language specific views (only returns records where userenv('LANG') matches an installed language in eBus) and Operating Unit specific views (only returns records relating to the "current" operating unit(s)). Are you querying a view or a table? Does "select userenv('LANG') from dual;" return different results in sqlplus vs SQL Developer / TOAD? Are you querying tables protected by virtual private database security? Do you have a login.sql that is modifying settings when logging in to sqlplus, but not other tools? In TOAD are you using the same Oracle client as you are using in sqlplus?
Being an Oracle eBusiness Suite site, the most common way I have seen this is when querying a language specific view and SQL Developer has used the regional settings to determine the default language settings which results in userenv('LANG') = 'GB' (for example), but the Oracle registry has language settings which result in userenv('LANG') = 'US'.
My problem is with the query results. When I use ‘where condition’ or few column names then provides me the results.
select * from table, is not working for any table. Whereas it is working on sqlplus from command prompt. Is it some thing related to buffer size of sql developer? My query is having approx 600 to 700 rows as result. May be the sql developer is having some problem while carrying these rows.
So in SQL Developer "select * from table_name" doesn't return anything (although shows as processing for some time before returning no records), but "select * from table_name where column_1 = 'ABC'" or "select column_1, column_2, column_3 from table_name" does?
When you run the "select * from table_name" in SQL Developer, do you end up with anything on the status bar? For example, "All Rows Fetched: 0" would be the normal result if the query doesn't return any rows. Do you get any errors? If you run with the console (<SQL Dev Dir>\sqldeveloper\bin\sqldeveloper.exe instead of <SQL Dev Dir>\sqldeveloper.exe) do you get any errors displayed on the console after running the query?
It does not end with any error. It keep continue to execute the query for long time.
I also have the same issue. Additionally, the sizes of the 7 columns have datatype varchar2(2000).
I tried to query using WHERE condition and worked with 1 to 3 records as output.
My question is, can SQL developer handle 2k records of these data type? When querying the table, the connection was dropped when parsing the data and it hanged.
Please help me to fix this issue.
How do tell that the connection has been dropped if SQL Developer has hung? Is the db session disappearing? Is it dying with an ORA-7445/600?
If you run SQL Developer with the console window (starting from <SQL Dev Dir>\sqldeveloper\bin\sqldeveloper.exe instead of <SQL Dev Dir>\sqldeveloper.exe), do you get any errors displayed on the console?
No, there is no any error. It works perfectly when I run other queries using where or specific data matching.
Also does not provide many queries like select * from table.
When you run "select * from table;" does the progress bar next to the Clear button on the SQL Worksheet toolbar keep bouncing back and forth?
Do you get any messages displayed on the status bar on the bottom of the screen?
Do you get a result if you run "select count(*) from table;"?
I have observed the connection dropped when our developer issue a select * from table in v$session. Just like Amit, all queries with WHERE condition with less that 10 records of output works fine but when issuing a fulll select, the SQL Developer session hanged and lost connection from the client and database sessions.
Presumably "select * from all_views;" will return lots of rows and fail. What happens if you just do "select count(*) from all_views;"? This should return a single row of output and so succeed based on the behaviour you have seen before.
When your DB session disappears from v$session, do you get any ORA-7445/ORA-600 errors in the DB's alert log?
If you run SQL Developer with the console window (starting from <SQL Dev Dir>\sqldeveloper\bin\sqldeveloper.exe instead of <SQL Dev Dir>\sqldeveloper.exe), do you get any messages displayed on the console window when your select fails?
We have not receive any ORA-error when we encounter this issue. I suspect that when you try to query the table with lots of columns with varchar2(2000), it cannot be handled by SQL developer.
Also, I have escalated this to Oracle support and they gave me a solution. Unfortunately, our developer agency haven't give any feedback on this.
Feel free to try this solution and let me know if it works on you. (i.e. Amit)
1 Edit the sqldeveloper.conf available under <sqldeveloper>\sqldeveloper\bin
2 Add JVM options:
3 Save the file.
4 Restart the sql developer.
I had same problem using E-business views.
The query "select userenv('LANG') from dual;" gave different results in sqlplus (US) and SQLDeveloper (GB) on my PC.
I Changed sqldeveloper setting from Tools->Preferences->database ->NLS Parameters from "English" to American" and it fixed the problem.
I have the same error, i query a simple/plain table (not a EBS one), with 27 columns and only 107 records, from SQL Developer 2.1 in Windows XP to an "Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production" database installed in Red Hat and it never finish, in SQLPLUS it woks properly.
So i followed the instrutions:
I ran the query:
select userenv('LANG') from dual;
In SQLPLUS(US) and SQL Developer(ESM), i had different values, so i changed the NLS values in SQL DEVELOPER and i restart it, i ran again the query , and this time i had the same value (US). Then i tried again the query (select * from centrodistribucion) and the same problem, the query never ends.
I notice the following, if i only select 14 columns the query works fine, but if a add another one, it never finish.
Next the describe of the table:
Nombre Nulo Tipo
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ID NOT NULL NUMBER(10)
EESQUEMAVENTAENUM NOT NULL NUMBER(5)
ADMINISTRADO NOT NULL NUMBER(1)
EMAIL VARCHAR2(50 CHAR)
GESTIONINV NOT NULL NUMBER(1)
NOMBRECENTRO VARCHAR2(100 CHAR)
OBSCONTACTO VARCHAR2(150 CHAR)
OBSGENERALES VARCHAR2(150 CHAR)
PREFIJOCENTRO VARCHAR2(50 CHAR)
USAWEBZER NOT NULL NUMBER(1)
PARTY_ID_CDEBS_FK NOT NULL NUMBER(15)
PARTY_SITE_ID_CDEBS_FK NOT NULL NUMBER(15)
ID_EMPRESA_FK NOT NULL NUMBER(10)
The diifferences in runtime you are experiencing could be caused by diffent execution plans.
Please test your select with the ALL_ROWS and than with the FIRST_ROWS(10) hint and see if there is any diffenrence.
SELECT /*+ALL_ROWS*/ * FROM ...
SELECT /*+FIRST_ROWS(10)*/ * FROM ...