This discussion is archived
6 Replies Latest reply: Mar 25, 2013 6:14 AM by yoonas RSS

Oracle 11g XE Connectivity Problems

997214 Newbie
Currently Being Moderated
Hi to all and thanks for the attention !

Quick facts

OS : Microsoft Server 2008 R2 (x64)
Oracle Version : 11g XE (x32)
Database name : XE
I've been fighting over with providers for a whole day and I can't seem to make this work out,
I have an application(Web, on a IIS 7) that connects to a schema, the connection uses a Oracle Provider For OleDB, well at first I was getting a "Provider not registered" error, that was easy I only had to make the app run on x32 and puf the error changed, so I quickly set up the Data Source ( with the data source manager located in WOW64 ) with a oracle ole db provider, no problems, I can click on it, it will ask me for a user/password and when I click test connection I works just like I would expect it to work, now back to the application, when i set the connection string up, I get a error from the app : ORA-12154 TNS:could not resolve the connect identifier specified. So went up and tried making a udl ( invoking again the x32 ), same error ORA-12154 so I started digging and so far so good what I got is, "try tnsping <instance_Name>", did it, and got an OK (40ms) (though I had to navigate to bin folder, which was not explained in the posts, perhaps this means something ?) and also registered the oraoledb11.dll with regsvr32
Oh ! And I can connect to the database with sqldeveloper
I know really little of Oracle, any thoughts ?

Thanks again !
  • 1. Re: Oracle 11g XE Connectivity Problems
    sb92075 Guru
    Currently Being Moderated
    ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
    ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
    ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
    TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
    The lookup operation fails because the name provided can NOT be resolved to any remote DB.
    The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
    The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
    The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
    So post the content of the sqlnet.ora file.
    TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
    http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
  • 2. Re: Oracle 11g XE Connectivity Problems
    997214 Newbie
    Currently Being Moderated
    Here's what I have in my sqlnet.ora, please noticed, the names.directory was something I read on a post and tried when already having this problem.
    I just don't understand how come the data source connection test works out.

    # This file is actually generated by netca. But if customers choose to
    # install "Software Only", this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.
    names.directory_path = (TNSNAMES,ONAMES,HOSTNAME)
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  • 3. Re: Oracle 11g XE Connectivity Problems
    sb92075 Guru
    Currently Being Moderated
    The content of sqlnet.ora looks OK & won't contribute to the ORA-12154 error
    And I can connect to the database with sqldeveloper
    This means that both the listener & the DB are up & operational.


    post content of tnsnames.ora file
  • 4. Re: Oracle 11g XE Connectivity Problems
    997214 Newbie
    Currently Being Moderated
    Here's the content of tnsnames.ora, and the app and the database are all on the same machine

    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )
  • 5. Re: Oracle 11g XE Connectivity Problems
    997214 Newbie
    Currently Being Moderated
    Still trying to solve this one out
  • 6. Re: Oracle 11g XE Connectivity Problems
    yoonas Expert
    Currently Being Moderated
    Hi,

    please post your connection string
    Do you have more than one installation of oracle?

    start an sqlplus session and try connecting both with TNS and EZCONNECT

    TNS
    username/<password>@xe

    EZCONNECT
    username/<password>@localhost:1521/XE

    you are able to connect from sqldeveloper in basic (connectiion type) not with TNS i guess.



    Regards
    Yoonas

Legend

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