1 2 Previous Next 29 Replies Latest reply: Feb 6, 2014 10:11 PM by Mike Kemp RSS

    New Connection dialog does not detect all TNSNames entries

    Mike Kemp

      I have an entry in my TNSNames file, entry_123_456.

       

      I have confirmed that the TNSNames file specified, is the same one configured under Tools.Preferences.Database.Advanced.Tnsnames Directory.

       

      In that dialog, the following settings are specified:

      Sql Array Fetch Size (between 50 and 200) => 50

      Display Null Using Background Color => NONE

      Use Oracle Client => Oracle Home:file:/C:/app/mkemp/product/11.2.0/

       

      Other than that that no setting has a specified value and no checkboxes are checked.

       

      That last one, "Use Oracle Client", only has a value because I had previously checked  the "Use Oracle Client" checkbox and clicked "Configure" to specify a value for this setting.

      Now that value is there and there is (apparently) no way to get rid of it.

      Nevertheless, the checkbox is no longer checked.

       

      Using sqlplus, I can successfully connect to the db in question using the parameter values taken from the TNSNames file in question.

       

      I match the TNSNames entry closely to other entries in the file to make sure the syntax is correct. It is.

       

      However, this TNSNames entry does not appear in the New Connection dialog when I specify Connection Type "TNS" and drop down the picklist "Network Alias".

       

      I can see no reason for it not to.

       

      Thank you.

       

      SQL Developer 4.0.0.13 Build MAIN-13.80.

        • 1. Re: New Connection dialog does not detect all TNSNames entries
          Siva-Oracle

          Please check whether the TNS entry in question have any comment(#). If yes then remove the comment text and check whether it is working.

          • 2. Re: New Connection dialog does not detect all TNSNames entries
            rp0428

            I match the TNSNames entry closely to other entries in the file to make sure the syntax is correct. It is.

             

            However, this TNSNames entry does not appear in the New Connection dialog when I specify Connection Type "TNS" and drop down the picklist "Network Alias".

            What 'TNSNames entry' are you talking about? Post the entry - SHOW us what you mean - don't just tell us.

            • 3. Re: New Connection dialog does not detect all TNSNames entries
              Mike Kemp

              Hi Siva-Oracle. Thank you for responding to my question!

               

              In response to your suggestion, there are no comments embedded in the entry.

               

              Thanks again.

               

              @rp0428: Here is the actual entry (exact proprietary details masked)

               

              mySID07 =

                (DESCRIPTION =

                  (ADDRESS = (PROTOCOL = TCP)(HOST = ourserver)(PORT = 1521))

                  (CONNECT_DATA = (SERVICE_NAME = mySID07)))

               

              Ah well - I just tried to insert an image of a snapshot of the "New / Select Database Connection" dialog box and it says "

               

              Unauthorized

               

               

               


              You don't have permission to do the requested action. You might need to log in to the system before you can continue.

               

              Access to this place or content is restricted. If you think this is a mistake, please contact your administrator or the person who directed you here."

               

              So much for that.

               

              Anyway, the entry is there and I believe that's what you asked for.

               

              Thank you as well.

              • 4. Re: New Connection dialog does not detect all TNSNames entries
                rp0428

                mySID07 =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = ourserver)(PORT = 1521))

                    (CONNECT_DATA = (SERVICE_NAME = mySID07)))

                Your entry has whitespace before and after each of the '=' signs.

                 

                A very common cause of ORA file issues is extra whitespace. Those files can be VERY sensitive to whitespace. If you look at the sample TNSNAMES.ORA file that comes with the software you will see NO extra whitespace like that.

                 

                Remove all of that whitespace.

                mySID07=

                  (DESCRIPTION=

                    (ADDRESS=(PROTOCOL=TCP)(HOST=ourserver)(PORT=1521))

                    (CONNECT_DATA=(SERVICE_NAME=mySID07)))

                That should fix the problem.

                 

                If it doesn't and you need to troubleshoot further then go back to the basics. Create a new *.ORA file and just put your one entry in it and work with that until it works properly.

                • 5. Re: New Connection dialog does not detect all TNSNames entries
                  Mike Kemp

                  Thank you rp0428.

                  I'll be stunned if this works, since ALL of the entries in the TNSNames file are formatted this way, and the rest of them work.

                  I made the change you suggested and am rebooting SQL Developer.

                  Thank you and I'll let you know!

                  Hey - trust me, I've been stunned before!!

                  • 6. Re: New Connection dialog does not detect all TNSNames entries
                    Mike Kemp

                    That did not work.

                     

                    I have now set the SQL Developer.Preferences.Database.Advanced."Tnsnames Directory" to a blank value and deselected "Use Oracle Client" checkbox and "Use OCI/Thick driver" checkbox.

                     

                    I searched my C-drive for all instances of a TNSNAME.ora file, ensuring there is only one there.

                     

                    It appears that the list of "Network Alias" values in the picklist in the "New / Select Database Connection" dialog is cached in the program somewhere.

                     

                    Is this the case, and if so, is there a way to dump that cache?

                     

                    Thank you.

                    • 7. Re: New Connection dialog does not detect all TNSNames entries
                      Siva-Oracle

                      Hi Mike,

                      I have copy pasted this entry in my tnsnames.ora file. Then started SQL Developer and I could see the tns entry in the new connection window.

                      Looks like there is no problem in this entry. There may be a problem in other entry.

                      Try this:

                      Only keep this entry in tnsnames.ora file and check whether it is working. If it is working then copy paste the other entries one by one and check it.

                      • 8. Re: New Connection dialog does not detect all TNSNames entries
                        rp0428

                        Mike Kemp wrote:

                         

                        That did not work.

                        Then you may have a hidden whitespace character between the last entry that works and the new one. Those ORA files HATE whitespace!

                         

                        Did you do what I suggested and start with a NEW file?

                        • 9. Re: New Connection dialog does not detect all TNSNames entries
                          Mike Kemp

                          Hi rp0428, Siva-Oracle:

                           

                           

                          Thank you for your determined help with this.

                           

                           

                           

                          @Siva: I tried what you suggest above. No luck.

                           

                           

                           

                          @rp0428: I did not try that (unless what Siva suggested counts).

                           

                           

                           

                          Here is my question now:

                          I literally have 0 [tnsnames.ora] files on my C-Drive. I renamed all to names like "tnsnames_bak_yyyymmdd_hhmiam.ora", "tnsnamesxxx.ora", "tnsnames.ora_disable", etc.

                          I reboot SQL Developer.

                          STILL the same list of SIDs appears in the "New / Select Database Connection" dialog as before.

                          If I can figure out WHERE it's getting this list of SID's from, maybe this will solve the other problem.

                           

                          Any ideas?

                           

                           

                           

                          Thank you!

                          • 10. Re: New Connection dialog does not detect all TNSNames entries
                            rp0428
                            Here is my question now:

                            I literally have 0 [tnsnames.ora] files on my C-Drive. I renamed all to names like "tnsnames_bak_yyyymmdd_hhmiam.ora", "tnsnamesxxx.ora", "tnsnames.ora_disable", etc.

                            I reboot SQL Developer.

                            STILL the same list of SIDs appears in the "New / Select Database Connection" dialog as before.

                            If I can figure out WHERE it's getting this list of SID's from, maybe this will solve the other problem.

                            That is what troubleshooting is all about - using FACTS to isolate the problem.

                             

                            I'm not aware of any caching of that file by sql developer.

                             

                            And if it isn't cached it means it is accessing another file from somewhere.

                            I have now set the SQL Developer.Preferences.Database.Advanced."Tnsnames Directory" to a blank value
                            . . .
                            I searched my C-drive for all instances of a TNSNAME.ora file, ensuring there is only one there.

                            So now you need to keep going:

                             

                            1. Set that preferences value to your 'empty' file and see if it finds it

                             

                            2. Search other drives that are accessible for the TNSNAMES.ORA file

                             

                            3. Remove sql developer completely (all files and folders) and set it up fresh.

                            • 11. Re: New Connection dialog does not detect all TNSNames entries
                              Kris Rice-Oracle

                              Try this:

                                   Kris' blog: Which tnsnames?

                               

                              I never knew it until we built this but tnsnames* is used by sqlplus and OCI. So, that's what sqldev does also, anything named tnsnames* is parsed and added to the drop list.

                               

                              -kris

                              • 12. Re: New Connection dialog does not detect all TNSNames entries
                                Mike Kemp

                                Wow man, that is freaky.

                                And cool.

                                That sheds a whole new light on things - and I mean that in a good way!!

                                Thank you.

                                • 13. Re: New Connection dialog does not detect all TNSNames entries
                                  Mike Kemp

                                  Stupid question (??):

                                   

                                  I read your blog.

                                  So how to get "Logging Page: Log" to show?

                                  I do SQLDeveloper.View.Log and all I get is "Messages - Log". I right-click everywhere and select a bunch of other choices under "SQLDeveloper.View" and no love at all.

                                  Does this thing hate me? Haha ... jk.

                                  But seriously, I don't see any "Logging Page: Log" (but I know I've seen it before).

                                   

                                  Thank you.

                                  • 14. Re: New Connection dialog does not detect all TNSNames entries
                                    Mike Kemp

                                    Nvmnd to my last question.

                                    I found the answer here:

                                    How to enable logging in SQL Developer - Method R

                                    Except I only modified line:

                                    # IncludeConfFile  sqldeveloper-nondebug.conf

                                    to

                                    IncludeConfFile  sqldeveloper-debug.conf

                                    Now I have the logging page.

                                    Thank you.

                                    1 2 Previous Next