Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com
Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

[Source: http://geekswithblogs.net/EltonStoneman]

The typical approach for connecting to Oracle data sources using ODP.NET is to use a named source in the connection string and rely on the name being resolved through SQLNAMES.ORA and TNSNAMES.ORA:

app.config:

  <connectionStrings>

    <add name="XYZ" connectionString="Data Source=XYZ.WORLD;User ID=xyz;Password=xyz;"/>

  </connectionStrings>

TNSNAMES.ORA:

XYZ.WORLD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (LOAD_BALANCE = off)

      (FAILOVER = on)

      (ADDRESS_LIST =

        (LOAD_BALANCE = on)

        (FAILOVER = on)

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ1-1.internal.xyz.org.uk)(PORT = 1522))

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ1-2.internal.xyz.org.uk)(PORT = 1522))

      )

      (ADDRESS_LIST =

        (LOAD_BALANCE = on)

        (FAILOVER = on)

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ2-1.internal.xyz.org.uk)(PORT = 1522))

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ2-2.internal.xyz.org.uk)(PORT = 1522))

      )

    )

    (CONNECT_DATA =

      (SERVICE_NAME = XYZ)

      (FAILOVER_MODE =

        (TYPE = select)

        (METHOD = basic)

        (RETRIES = 16)

        (DELAY = 1)

      )

    )

  )

 

This is fine until you upgrade your Oracle client, or have multiple instances – if the wrong TNSNAMES gets picked up you’ll get error ORA-12514: TNS:could not resolve the connect identifier specified, and it can be fiddly to track down.

A neater way is to put the whole set of service details in the connection string, avoid the named identifier and circumvent TNSNAMES.ORA altogether:

<connectionStrings>

    <add name="XYZ" connectionString="Data Source=(DESCRIPTION =

    (ADDRESS_LIST =

      (LOAD_BALANCE = off)

      (FAILOVER = on)

      (ADDRESS_LIST =

        (LOAD_BALANCE = on)

        (FAILOVER = on)

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ1-1.internal.xyz.org.uk)(PORT = 1522))

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ1-2.internal.xyz.org.uk)(PORT = 1522))

      )

      (ADDRESS_LIST =

        (LOAD_BALANCE = on)

        (FAILOVER = on)

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ2-1.internal.xyz.org.uk)(PORT = 1522))

        (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ2-1.internal.xyz.org.uk)(PORT = 1522))

      )

    )

    (CONNECT_DATA =

      (SERVICE_NAME = UVASA)

      (FAILOVER_MODE =

        (TYPE = select)

        (METHOD = basic)

        (RETRIES = 16)

        (DELAY = 1)

      )

    )

  );User ID=xyz;Password=xyz;"/>

  </connectionStrings>

Service details contain the whole of the TNSNAMES entry following the identifier (i.e. from DESCRIPTION= onwards), and can span multiple lines in the config file.

Incidentally, if you’re not using ODP.NET in favour of the Microsoft’s framework Oracle provider, System.Data.OracleClient, consider migrating as it will be deprecated from .NET 4.0.

Posted on Thursday, November 5, 2009 10:03 PM Oracle | Back to top


Comments on this post: Avoiding TNSNAMES in Oracle .NET data connections

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
That works fine if you've got one or two applications; but when you've got hundreds of applications connecting to a massive variety of oracle databases, you'll find the tnsnames file makes your life a lot easier for upgrades, server errors, system re-routes and the like.

I much prefer having the DBAs update one location and everything changes than having to individually update every application for what is essentially a configuration change.
Left by Colin Morris on Nov 29, 2009 5:46 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
It sounds like a great application.I am pretty much pleased with your good work.Thanks a lot for sharing.Keep up the good works.
Left by home security systems13 on May 26, 2010 5:39 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
Left by scrapbook pages554 on Jul 08, 2010 7:38 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I must say that elements you put here look awesome. I liked all of them. Keep it up. Thanks a lot for sharing.... Looking forward to reading your next post..
Left by diabetes on Oct 20, 2010 3:12 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
There is obviously a lot to know about this. I think you made some good points in Features also.
Keep working ,great job!
Left by h1n1 virus on Nov 19, 2010 7:42 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I am developing an application in VB .NET and Oracle 8i. I am exactly not sure about what provider should I use for my database. Which one would give the best performance... Any help would be appreciated. Thanks.
Left by medieval times coupon on Jan 07, 2011 8:30 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
great info by the way
Left by advanced seo on May 13, 2011 11:56 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Thanx for the post, we will come back to see if you have any more interesting follow up topics ;-) London escorts Indian
Left by London Escorts on Jun 05, 2011 12:37 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Your tips are extremely valuable. I am pretty much pleased with your good work. Looking forward to reading your next insightful posts.
Left by motorcycle battery on Jun 25, 2011 4:17 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
wow great i have read many articles about this topic and every time i learn something new i dont think it will ever stop always new info , Thanks for all of your hard work!
ipad 2 keyboard case
Left by roger on Sep 02, 2011 4:10 AM

# Nice
Requesting Gravatar...

I wish more people would write sites like this that are actually interesting to read. With all the fluff floating around on the net, it is a great change of pace to read a site like yours instead.
Left by Estudiar ingles en el extranjero on Nov 20, 2011 12:43 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Thanks for this amazing share.. this post helped me a lot in my project.. Great work.. scrabble cheat
Left by sony on Nov 22, 2011 11:22 AM

# Info
Requesting Gravatar...

This is strange thing.Your observation is fine from one corner if we see things.Lots of people do have this view but things are like that.
Left by ATL Escorts on Nov 23, 2011 12:33 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
These techniques are quite useful.I never thought we can do things in this way.Hats off to your brain.I would like to share this on my blog.
Left by unlock iphone on Dec 13, 2011 5:30 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
This code is going to useful for me for my next project. thanks for sharing it.
Left by booklet printing on Dec 14, 2011 8:33 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I am just learning oracle,after finishing it will pursue .net.
Its my second month learning it. You know what i learned very important things with this program.I became a regular user of this site.And i am excited to see many such programs which are simply piece of a master.
Left by Jamie Sanger on Dec 16, 2011 12:37 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
How to upgrade to oracle client i find some errors and is there any other way to avoid TNSNAMES.
Left by Santo Kitanik on Dec 22, 2011 10:18 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
The tutorials are quite handy.I am excited to implement them and find out the results.
Left by Lipozene on Mar 24, 2012 11:51 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Wonderful posts you have on your blog and I have bookmarked you and will visit yours often.
Left by Pure Berry Max on Apr 28, 2012 4:41 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
This site appears to get a great deal of visitors. How do you promote it? It offers a nice unique twist on things. I guess having something useful or substantial to talk about is the most important thing.
Left by Xenadrine on Apr 29, 2012 3:48 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
You rock it up and keep rocking. That was pretty good information. Wonderful to read your post
Left by Hydroxycut on May 06, 2012 6:18 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Now I know it and will tell to my friends about this blog
Left by Lipozene Review on May 20, 2012 10:10 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Hey that was great to read. Thanks for the great post .Loved every part of it.
Left by tantric massage in london on May 21, 2012 1:25 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Terrific way of expressing those things in your post. Clear cut meanings.
Left by Alli Review on May 26, 2012 4:18 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I will visit again, keep updating your blogs. I like it.
Left by Lipozene Reviews on Jun 12, 2012 9:02 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I wanted to write a quick note to express my thanks. I'm really impressed by a blog unique and perfectly chosen and organized! thx
Left by Lipozene on Jun 14, 2012 5:00 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I am very much overwhelmed by your thoughts for this particular story. A more deeper and staged knowledge would be good for me
Left by Hydroxycut on Jun 17, 2012 1:16 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I guess having something useful or substantial to talk about is the most important thing.
Left by Lipofuze on Jun 24, 2012 7:44 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Thanks so much with this fantastic new web site. I’m very fired up to show it to anyone. It makes me so satisfied your vast understanding and wisdom have a new channel for trying into the world.
Left by Lipozene Reviews on Aug 03, 2012 8:10 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
This is beyond doubt a blog significant to follow. You’ve dig up a great deal to say about this topic, and so much awareness. I believe that you recognize how to construct people pay attention to what you have to pronounce, particularly with a concern that’s so vital. I am pleased to suggest this blog.
Left by Hydroxycut on Aug 03, 2012 2:56 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Just stumbled across your blog and was instantly amazed with all the useful information that is on it. Great post, just what i was looking for and i am looking forward to reading your other posts soon!
Left by Lipozene on Aug 05, 2012 7:39 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Now and then I’ll stumble across a post like this and I’ll recall that there really are still interesting pages on the web. ^_^. Thanks.... very useful
Left by Fast Weight Loss on Aug 13, 2012 9:45 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I think the natural and biological sources of vitamins and minerals are best compared with other packaging and plastic products. Always try to push things you need in your home. It is not only going to be cheaper, but it will be much more beneficial to your health and your family. :)
Left by London service on Sep 02, 2012 12:59 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
I am very much overwhelmed by your thoughts for this particular story. A more deeper and staged knowledge would be good for me thank you..
Left by Lipozene on Sep 11, 2012 12:50 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
This is such a Great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It’s the old what goes around comes around routine
Left by contact us on Sep 18, 2012 4:34 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
All the contents you mentioned in post are too good and can be very useful. I will keep it in mind, thanks for sharing the information keep updating, looking forward for more posts. Thanks
Left by Lipozene on Oct 03, 2012 12:48 PM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking
Left by Raspberry Ketone on Oct 08, 2012 9:50 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
That's wrong told I started reading this blog post I couldn't stop until I was done, while it wasn't just what I had been trying to find, was still a great read though.
Visit
Left by Visit on Nov 30, 2012 10:19 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
Great post, just what i was looking for and i am looking forward to reading your other posts soon. Karen Millen Dresses Australia
Left by Karen Millen Australia on Feb 23, 2013 6:39 AM

# re: Avoiding TNSNAMES in Oracle .NET data connections
Requesting Gravatar...
well done . thanks
Left by chamika on Jan 03, 2015 6:07 AM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net