7 Replies Latest reply on Mar 6, 2018 2:54 PM by thatJeffSmith-Oracle

    Connection type TNS - source of 'network alias' entries

    EdStevens

      When editing the properties of a connection, where does SQL Dev get the values that it uses to populate "Network Alias" drop-list?  I always assumed it came from the relevant tnsnames.ora file.  In my (our) case, this is a shared file on a server, indicated by setting TNS_ADMIN as a system variable on the Windows desktops.

       

      Yesterday I did some editing of that file, and this morning one of my developers reports an ORA-12514 - listener does not know of requested service.  But sqlplus has no issue on the same desktop.  It only occurs on one particular db connection.

       

      On my own desktop I use 'basic' connections, but when I tested TNS, I got the same result - this one particular db returns ORA-12514.  And what's really curious is when I look at the pick-list, I see every entry repeated, and some entries that do not exist in the shared tnsnames.ora:

       

        • 1. Re: Connection type TNS - source of 'network alias' entries
          thatJeffSmith-Oracle

          In your sql worksheet, run show tns...works in SQcl too

           

          show-tns.png

          • 2. Re: Connection type TNS - source of 'network alias' entries
            EdStevens
            1. Hmm.

            I don’t have sqlcl installed, but in the worksheet, I get:

             

             

             

            In the referenced location, have several backups of tnsnames, some made by me and some apparently by netca.

             

             

            On a guess, I renamed the ‘tnsnames.ora.yyyymmdd’ to ‘tnsnames_yyyymmdd.bak’ and repeated the ‘show tns’.  Same result.  So guessing that SQL Dev cached the results, I restarted it.  This time the result was correct.  And the test of the bad connection was now a success.

             

            So it appears that SQLDev is loading up and caching the contents of any file named ‘tnsnames.ora’ OR ‘tnsnames.ora.*’.  Hmmm… I think I'd consider that to be a bug.

             

            And if it gets duplicates (as would be expected in that case) how does it determine the order of precedence?  In the case of our problematic connection, that particular DB had been migrated to a different server quite some time back, so one of the older backup tnsnames had the wrong server, thus the error. But when I stepped through testing each of the four duplicate entries, they all failed.  So I’d have to conclude that even though SQL Dev is populating the list with entries from every copy of tnsnames it used, it only actually caches one name resolution, and it may not be the correct one.

            ?!?!

            • 3. Re: Connection type TNS - source of 'network alias' entries
              Gary Graham-Oracle

              So it appears that SQLDev is loading up and caching the contents of any file named ‘tnsnames.ora’ OR ‘tnsnames.ora.*’.  Hmmm… I think I'd consider that to be a bug.

              As far as I know, that is the way (based on the TNS_ADMIN environment variable) it has always been with SQL Developer, with the ability to specify a particular tnsnames directory in the Database > Advanced preferences in the 2.x or 2.1.x time-frame.  Jeff commented on this a few years back... https://www.thatjeffsmith.com/archive/2014/06/on-sql-developer-and-tnsnames-ora/

              • 4. Re: Connection type TNS - source of 'network alias' entries
                EdStevens

                Gary Graham-Oracle wrote:

                 

                So it appears that SQLDev is loading up and caching the contents of any file named ‘tnsnames.ora’ OR ‘tnsnames.ora.*’. Hmmm… I think I'd consider that to be a bug.

                As far as I know, that is the way (based on the TNS_ADMIN environment variable) it has always been with SQL Developer, with the ability to specify a particular tnsnames directory in the Database > Advanced preferences in the 2.x or 2.1.x time-frame. Jeff commented on this a few years back... https://www.thatjeffsmith.com/archive/2014/06/on-sql-developer-and-tnsnames-ora/

                Ouch!  Seems that is by design?

                 

                 

                The File(s)

                That’s right, files. Just like SQL*Plus, we’ll read any file that starts with ‘tnsnames’ – that includes files you’ve renamed to .bak or .old.

                Actually, I found it didn't work quite that way.  I was not picking up files name 'tnsnams<something>.bak'.  But it was picking up 'tnsnames<something>.ora'.

                 

                Either way, in spite of the statement that it knowingly behaves that way, I'd consider it a bug or a poorly thought-out design decision.  The whole purpose of naming a file <something>.bak is to make a backup copy and get it out of the way.  And it works that way for all other clients that I know of.  But here we're saying that SQL Dev is going to deliberately override that and pull in a bunch of files that I am purposely wanting to ignore.  The whole world knows the file name is 'tnsnames.ora'.  Why would SQL Dev want to get 'any file that simply begins with 'tnsnames'?

                • 5. Re: Connection type TNS - source of 'network alias' entries
                  Gary Graham-Oracle

                  I cannot really argue in favor of the current behavior.  Others, of course, have commented over the years, as in...

                  Connection strings in SQL developer

                   

                  Personally, I love using the TNS connection type in SQL Developer and always keep a tnsnames.ora file about somewhere, even when I have no Oracle client installed.  There was one discussion that I am unable to find just now where the poster kept all backup tnsnames files in a separate folder -- an extra step, but it works.

                   

                  Anyway, I wouldn't bother logging a bug on this one -- there is hope on the horizon!  There are plans in a future release to support loading a specific tnsname.ora file, rather than just pointing at an entire directory.

                  • 6. Re: Connection type TNS - source of 'network alias' entries
                    jflack

                    thatJeffSmith-Oracle once answered about the reasoning behind using all tnsnames.ora* files, which would include backup files.  It isn't a bug, it is a WAD (works as designed).

                     

                    Jeff said that the reason is that they were trying to duplicate what SQL*Plus does as closely as possible, and SQL*Plus does the same thing.  I think the reason you don't notice is as much with SQL*Plus is that you never get a list of connections to choose from.  Instead, SQL*Plus reads TNSNAMES.ORA first - before the other files with the similar names, and if it finds the connection you wanted, it stops looking.  But if you were looking for MYORACLE and TNSNAMES.ORA didn't have an entry by that name, SQL*Plus might look in TNSNAMES.ORA.BAK next.

                    • 7. Re: Connection type TNS - source of 'network alias' entries
                      thatJeffSmith-Oracle

                      18.1 - you'll be able to pick a specific tns file to define a connection from. Ignore the styling below - that's still a work in progress.

                       

                      whoa.png