14 Replies Latest reply: Sep 29, 2011 7:22 AM by Matt Schulz RSS

    SQL Developer not providing query results.

    713678
      I am having a weird problem. I have one query which provides me some records from my database (approx 700-800 rows). I use Oracle Sql developer or Toad as database tool, the problem is with the results. When I run the same query directly on sqlplus prompt then it displays complete results, therefore I can say that query has no problem. But when I run the same query with SQL Developer then it does not provide me any result, and shows status as processing for long time. Please help me I am running on Oracle 10g.
        • 1. Re: SQL Developer not providing query results.
          561237
          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'.

          theFurryOne
          • 2. Re: SQL Developer not providing query results.
            713678
            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.
            • 3. Re: SQL Developer not providing query results.
              561237
              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?

              theFurryOne
              • 4. Re: SQL Developer not providing query results.
                713678
                It does not end with any error. It keep continue to execute the query for long time.
                • 5. Re: SQL Developer not providing query results.
                  450702
                  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.

                  Thanks
                  • 6. Re: SQL Developer not providing query results.
                    561237
                    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?

                    theFurryOne
                    • 7. Re: SQL Developer not providing query results.
                      713678
                      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.
                      • 8. Re: SQL Developer not providing query results.
                        561237
                        Amit,

                        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;"?

                        theFurryOne
                        • 9. Re: SQL Developer not providing query results.
                          450702
                          Hi,

                          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.

                          Thanks.
                          • 10. Re: SQL Developer not providing query results.
                            561237
                            Zyc,

                            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?

                            theFurryOne
                            • 11. Re: SQL Developer not providing query results.
                              450702
                              Hi Furryone,

                              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:

                              AddVMOption -Xmx1024M
                              3 Save the file.
                              4 Restart the sql developer.

                              Regards,
                              Zyc
                              • 12. Re: SQL Developer not providing query results.
                                R12.Appsdba-Oracle
                                Thanks theFurryOne.
                                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.

                                Regards,
                                Amandeep Singh
                                • 13. Re: SQL Developer not providing query results.
                                  144629
                                  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 11.2.0.1.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.

                                  Any idea?

                                  Next the describe of the table:

                                  desc centrodistribucion
                                  Nombre Nulo Tipo
                                  ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  ID NOT NULL NUMBER(10)
                                  ACTIVO NUMBER(1)
                                  ID_USRCR_FK NUMBER(10)
                                  FEC_CREACION DATE
                                  ID_USRAC_FK NUMBER(10)
                                  FEC_ULTACT DATE
                                  EESQUEMAVENTAENUM NOT NULL NUMBER(5)
                                  ADMINISTRADO NOT NULL NUMBER(1)
                                  EMAIL VARCHAR2(50 CHAR)
                                  FRECCONTEOENUM NUMBER(5)
                                  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_DIR_FK NUMBER(10)
                                  ID_EMPRESA_FK NOT NULL NUMBER(10)
                                  ID_PERAD_FK NUMBER(10)
                                  ID_ZOORG_FK NUMBER(10)
                                  ID_ZOTER_FK NUMBER(10)
                                  NOREMPED NUMBER(10)
                                  NOREMDEV NUMBER(10)
                                  CATDIST VARCHAR2(30)
                                  SURTIDOAUT NUMBER(1)
                                  • 14. Re: SQL Developer not providing query results.
                                    Matt Schulz
                                    Hi!

                                    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.

                                    e.g.:

                                    SELECT /*+ALL_ROWS*/ * FROM ...

                                    and

                                    SELECT /*+FIRST_ROWS(10)*/ * FROM ...

                                    Best regards,
                                    Matt