This discussion is archived
11 Replies Latest reply: Mar 10, 2012 3:00 PM by user515689 RSS

unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..

907806 Newbie
Currently Being Moderated
hi all
i used Windows 7 and i used client Oracle 11.2.2 Win_Client 32 when i m going to create ODBC its make error unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..

but i used sql developer i can see all of my table form server.

any one help me how can i slove my problem.

thanks in advance
  • 1. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    orafad Oracle ACE
    Currently Being Moderated
    Please detail url of Instant Client packages you downloaded and installed, and ODBC driver name from ODBC DSN admin tool.

    The ORA-12154 generally means that connect identifier, e.g. TNS Service Name in ODBC driver dialogue, could not be resolved. So a request never left the client side, because connect info was incorrect or incomplete.

    You could use the Easy connect naming, i.e. hostname:port/servicename to avoid potential tnsnames.ora issues.
  • 2. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    Hopefully I'm not hijacking the thread, but it looked like it had not been answered andI have a very similar scenario brewing here. I'm trying to connect an Oracle 11.1 server to an Oracle 9 client in Access on a Windows server (2003, I believe). The Office suite version, with Access, is 2003.

    I get this same error.

    Are you suggesting that instead of the SID in the TNS Service name field we simply put hostname:port/servicename ?

    Is this correct, or at least what you meant to say?

    Please clarify if not.

    Thanks.
  • 3. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    orafad Oracle ACE
    Currently Being Moderated
    Even if "very similar ..." often is far from enough, and from what you have posted unrelated to Instant Client, which is the topic of this forum,

    The 12154 is very much a Faq, and it basically says error in connect string; with config in use, the name/connect identifier passed could not be resolved (e.g. no or wrong tnsnames.ora found).

    Easy connect as a different naming method (different way of resolving identifier to connect descriptor) could serve as a workaround (at least).
    Put e.g. host1:1521/xe or host1/xe for hostname:port/service name. Of course, one would need to put actual details from the system or setup.

    Note that Easy Connect naming may not exist so is not valid in your Client version!

    Putting SID is incorrect (it may have been the same name as a SID but never SID) and using SID in general instead of Service name post Oracle8 is likely incorrect usage.

    Edited by: orafad on Feb 13, 2012 10:07 AM

    Edited by: orafad on Feb 13, 2012 10:08 AM
  • 4. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    Ok, sorry, I had misunderstood, initially, that it was about InstantClient.

    My apologies.

    InstantClient may not be an option for me, but I'm going to have the engineers install a newer Oracle driver that will work.
  • 5. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    orafad Oracle ACE
    Currently Being Moderated
    >
    InstantClient may not be an option for me, but I'm going to have the engineers install a newer Oracle driver that will work.
    Well, take a look at a Instant Client Light packages, e.g. instantclient-basiclite-nt-11.2.0.2.0.zip, from the IC for Win 32-bit downloads page -
    http://www.oracle.com/technetwork/topics/winsoft-085727.html

    11.2.0.2 should be supported on Server 2003 and Server 2003 R2, so go with the current version.

    The fact that Instant Client is just 3-4 .dll files to just drop in program cwd or a system/user PATH directory may be very appealing over a rather heavy install for the "full" Database Client, specially on a Server host os. The (separate) ODBC package is a small registry installer to "add" the driver entries to MS ODBC Admin.

    Depending on db API/type of data source used by application program i.e. Msaccess, you might want to look at ODAC xcopy deployment package. ODAC builds on Instant Client and includes e.g. Ole Db data provider, I belive.
    http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html

    Edited by: orafad on Feb 15, 2012 2:08 PM

    Edited by: orafad on Feb 15, 2012 2:08 PM
  • 6. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    Thank you, orafad.


    I'll look into this. It does sound more appealing, although in our environments I don't know if it's been tested.

    As opposed to the Oracle clients, I don't know how comfortable they'd feel installing this, even though it's much more lightweight.
    Do the servers need a recycle upon installing? That kind of thing.

    Could it jeopardize any other existing Ora client or anything like that possible?

    Thanks.
  • 7. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    Hi orafad,

    I'm looking into having this done, deployed to a server.

    Here's another question though. Many users and groups acess MS Access via a shared server, on a Citrix thin client setup. Many of these still access other Oracle servers via ODBC where the Oracle servers are still on 10g versions.

    Would this cause any conflict adding this instant client app to be able to access Oracle11g servers via ODBC?

    I haven't come across anything yet saying so either way.

    Thanks.
  • 8. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    Well, I've done this with mixed results!

    I was so ecstatic about this when I first deployed it on my Win XP box, here at work. It did great!
    Very lightweight, and it did not even interfere with my previous Oracle 10 client installation. I could still open previously configured ODBC connections I'd made with the 10 client.

    Fast forward to yesterday. I've tried this on 3-4 co-workers' desktops with no success. I often get the Could not Load Library or code 126 error messages. I've been following the Read Me file to a "t" and have not been able to crack it. Most of these boxes either have the VC++ Runtime Distro package, or we had it put on there. Still, no joy!

    Also, we tried one of the main Win2003 per-prod servers yesterday. The engineer and I spen an hour to two hours working on this, and could not get it to connect at all.

    It works fine for me, and I'm at a loss to figure out how it works for me, yet I can't get it to work on other desktops (or that one server) despite unzipping the two needed packages, instantclient-basiclite-nt-11.2.0.2.0 and instantclient-odbc-nt-11.2.0.2.0.
    I unzipped both to the same directory, but no luck after double clicking the odbc exe.
    The ODBC Console "sees" the InstantClient driver, but just can't connect.

    We often get the "no listener" error or error 14001 or code 126.

    If you have any insight into this inconsistency, please do tell!

    Than you, orafad!
  • 9. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    >
    Even if "very similar ..." often is far from enough, and from what you have posted unrelated to Instant Client, which is the topic of this forum,

    The 12154 is very much a Faq, and it basically says error in connect string; with config in use, the name/connect identifier passed could not be resolved (e.g. no or wrong tnsnames.ora found).

    Easy connect as a different naming method (different way of resolving identifier to connect descriptor) could serve as a workaround (at least).
    Put e.g. host1:1521/xe or host1/xe for hostname:port/service name. Of course, one would need to put actual details from the system or setup.

    Note that Easy Connect naming may not exist so is not valid in your Client version!

    Putting SID is incorrect (it may have been the same name as a SID but never SID) and using SID in general instead of Service name post Oracle8 is likely incorrect usage.



    And one more followup. When I have Instant Client loaded on a machine, should I be uising this method to connect?

    host1:1521/servicename or just SID?
    My local desktop accepts the SID and works. but others have not.

    Thanks.
  • 10. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    orafad Oracle ACE
    Currently Being Moderated
    Please start a new thread for your issue, as what you have seem to be different from op and subject of the thread.

    You could just copy the text of the post (to which this a reply) to the new thread. But please include full error messages and at exactly what step of ODBC config routine you get the error message box.

    Hint: error code 126 often means install problems such as file not found or not accessible (e.g. due to file permissions).
  • 11. Re: unable to connect SQLState=08004 Oracle ODBC ORA-12154:TNS..
    user515689 Newbie
    Currently Being Moderated
    OK, thank you for the suggestions! I will do so.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points