10 Replies Latest reply on Jul 21, 2017 10:31 PM by Gary Graham-Oracle

    SQL Developer vs. SQL Navigator

    EdStevens

      Is anyone familiar enough with both SQL Dev and Quest's "SQL Navigator"  (actually, I think Dell owns it now).

       

      We are reviewing our licensing for SQL Nav and looking to reduce or eliminate it to save $$.  For myself, there are a couple of nice features in SQL Nav that I've not found duplicated in SQL Dev (4.0) but I'll be the first to admit I've not really explored very much.

       

      The first feature is a 'code search'.  Enter a string, and it will list all pl/sql procs, pkgs, triggers, etc that have that string, with a little bit of context.  Configurable for exactly how it determines a match and the scope of the search.

       

      The other feature is 'session browser'.  Lists all the sessions.  Basic list shows username, schema, status, os user, client machine, terminal, SID, logon time, client executable pgm, etc, with drill down tabs for various items.  Of these, the ones I make good use of are

       

      • IO
        • block gets
        • consitent gets
        • physical reads
        • block changes
      • waits
        • event
        • wait_time
        • second_in_wait
        • state
        • p1,2,3 and their text
      • current sql
      • locks
      • long ops

       

       

      If the above looks like a plug for SQL Nav, it is certainly not meant to be.  I'd actually like to get rid of it and save the money, if I can be shown that the above functionality is available in SQL Dev.

      Other than the above, the use of SQL Nav is really just as a GUI coding environment, so moving the developers to SQL Dev would just be a little learning curve to get used to the new GUI.

        • 1. Re: SQL Developer vs. SQL Navigator
          thatJeffSmith-Oracle

          I'm pretty familiar with both tools.

           

          They're not owned by Dell anymore FWIW..

           

          1)Code Search, yes.

           

          Hit the binoculars button. You can search your Views, PL/SQL, and REST Services. I can talk about that more here

           

          2)Session Browser, yes.

           

          Tools > Monitor Sessions. If you don't like our report, you can change it up to show exactly what you want.

           

          Also, version 4.0 is OLD. Very old. Go get version 17.2.

          • 2. Re: SQL Developer vs. SQL Navigator
            EdStevens

            thatJeffSmith-Oracle wrote:

             

            I'm pretty familiar with both tools.

             

            They're not owned by Dell anymore FWIW..

             

            1)Code Search, yes.

             

            Hit the binoculars button. You can search your Views, PL/SQL, and REST Services. I can talk about that more here

             

            2)Session Browser, yes.

             

            Tools > Monitor Sessions. If you don't like our report, you can change it up to show exactly what you want.

             

            Also, version 4.0 is OLD. Very old. Go get version 17.2.

            Thanks, Jeff.  I'll give it a look-see.

            • 3. Re: SQL Developer vs. SQL Navigator
              EdStevens

              Downloaded latest.  Overall I like what I see, especially some of the new "dba" oriented stuff.  Looks like it will at least partially fill the gap with the loss of dbcontrol when I complete upgrades to 12c.

               

              But I'm not seeing the code search functionality I'm looking for.  Perhaps I'm not in the right area, not making a correct selection, or just not recognizing it as such.

               

               

              At the time I click it, I have one open connection.  I get

               

              With a short pop-up telling me to enter the name of "an object".

              I'm not searching for objects.  What I'm after is something like this:

               

              "show me all occurrences of the string 'xyz' in any package, procedure, function, trigger, etc, in any schema in this database."

              And get in return a list of code type, owner,and name, line number, and the actual line of code, showing the occurrence of 'xyz' in context.

              • 4. Re: SQL Developer vs. SQL Navigator
                Gary Graham-Oracle

                From the main menu bar, try View > Find DB Object to get the full search functionality.

                • 5. Re: SQL Developer vs. SQL Navigator
                  thatJeffSmith-Oracle

                  You have to check the code box, expand it to see your options. You can search object names, code, or both in a single search.

                  • 6. Re: SQL Developer vs. SQL Navigator
                    EdStevens

                    That got even "curioser and curioser".

                     

                    First, it popped up a connection request for a different database:

                     

                    Why would it insist on trying to connect to some other database?  It didn't even ask which one.

                    clicking 'cancel' resulted in this:

                    Clicking OK brought back the 'connection information' window. Clicking 'cancel' again produced the error again. Clicking OK again finally gave me the same search window as before.

                    Ok, taking a closer look, and getting beyond the statement to "find database object" (emphasis mine) I see the 'code' box.  So opening it and making some more selections, attempt to search for some code that I know exists.  But it fails to find it:

                     

                     

                    • 7. Re: SQL Developer vs. SQL Navigator
                      Gary Graham-Oracle

                      Not sure about the empty Connection Info prompt but, as for not finding anything, try unchecking the ORDS checkbox -- there was a bug reported on that recently.

                      • 8. Re: SQL Developer vs. SQL Navigator
                        Marwim

                        If the existing functionality is not good enough you can always write your own search. I wrote my own search (among other things) as user defined report

                        You can (but don't have to) limit the search to certain objects. The result lists every hit and in a child report the previous and following 30 lines

                        Or a custom session browser

                         

                        http://www.thatjeffsmith.com/archive/2014/04/oracle-sql-developers-best-kept-secret-reports/

                         

                        Regards

                        Marcus

                        • 9. Re: SQL Developer vs. SQL Navigator
                          EdStevens

                          Gary Graham-Oracle wrote:

                           

                          Not sure about the empty Connection Info prompt but, as for not finding anything, try unchecking the ORDS checkbox -- there was a bug reported on that recently.

                          That did it!

                           

                          Plus, I see that in the resulting list, when I click on a reported line of code, it opens another tab with the entire procedure.  That's one thing that my developer was concerned about losing.

                           

                          Now, how do we deal with that bogus connection/credential pop-up.  It is very consistent, and is always for the same database/connection definition.  Is there some method by which I can/should report that as a potential bug?

                          • 10. Re: SQL Developer vs. SQL Navigator
                            Gary Graham-Oracle

                            how do we deal with that bogus connection/credential pop-up.  It is very consistent, and is always for the same database/connection definition.  Is there some method by which I can/should report that as a potential bug?

                            Strange.  Even if I already have opened a worksheet on a connection, when I open View > Find Database Object I must first select a connection before a click on Go will do anything (the already open connection does appear at the top of the connection selection list, as expected)...

                            FindRemembersPriorSettingsExceptConnection.jpg

                            It is true selecting a connection in Find then deciding to cancel it erroneously produces a ConnectionCanceledException dialog, so that is a bug.  Another fact, not totally relevant here, is that if you have a long running SQL statement executing in the worksheet, we see that Find must wait since it uses the shared connection, as here...

                            FindWaitingOnWorksheetQuery.jpg

                            So a Connection Information prompt for a connection you haven't requested should never appear, and only appear if the user or password is left blank for one you do select.  It seems something is corrupt.  You could test if one of these eliminates the bogus prompt...

                            1. Tools > Features > Clear cache

                            2. Delete the system_cache (and system_cache_1, etc) under your user setting's system17.2.0.188.1159 folder.

                            3. Rename (or delete) your user setting's system17.2.0.188.1159 folder and restart SQL Developer with or without migration of user settings.