This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 1, 2012 3:23 PM by CrackerJack RSS

ODBC connection ORA-12154 Sql State:8004

CrackerJack Newbie
Currently Being Moderated
Hi All ,

Tried setting up Oracle ODBC Driver Configuration with TNS Service Name but it was not able to recognise the tnsnames.

tnsnames.ora
-------------------
NRP45P_APP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora-svc-NRP45P-APP.extend.local)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = NRP45P_APP.extend.local)
))

I am

Able to connect via SQL Plus ( but SQL plus connects as ROBBOm/Anger_me1@'(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = ora-svc-NRP45P-APP.extend.local)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = NRP45P_APP.extend.local)))' )- notice teh single quotes
Able to connect to SQL Developer using just hostname and service name - no single quotes

I wanted to put the string of SQL PLus connection ( with single quote) but in TNS Servic ename but seems to have a limit of character .

Pleasse help me undertstand , tried two days with no success.Also had no success connecting via a JDBC driver which is why i though ODBC will be more better.
  • 1. Re: ODBC connection ORA-12154 Sql State:8004
    rp0428 Guru
    Currently Being Moderated
    >
    Also had no success connecting via a JDBC driver
    >
    Why not? What did you try? A JDBC driver doesn't need to use a TNSNAMES entry. It can use the host name, port and SID or service_name.

    Do you have a firewall that might be getting in the way? Or are you using a proxy?
  • 2. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    Hi Rp,

    There is a data importer GUI given by the vendor and this is the current

    JDBC connection string
    --------------------------------------
    Oracle = jdbc:oracle:thin:@server:port:db

    Java_Class
    ------------------
    Oracle = oracle.jdbc.driver.OracleDriver

    Java Database Driver:
    Oracle ojdbc14.jar

    When I fill up:

    Username:NJK_root
    Password:Cross_x123
    Host:ora-svc-NRP45P-APP.extend.local
    TCP Port:1521
    Database Identifier:NRP45P_APP.extend.local

    This error comes up:

    Could not conect to database:oracle.jdbc.driver.Oracle Driver jdbc:oracle:thin:@ora-svc-NRP45P-APP.extend.local:1521:NRP45P_APP.extend.local NJK_root Cross_x123

    Vendor also confirm the GUI works with 11G.

    My oracle version is 11.2

    Edited by: CrackerJack on Oct 31, 2012 7:41 PM
  • 3. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    But how did sql plus and sql developer bypassed the proxy and firewall?
  • 4. Re: ODBC connection ORA-12154 Sql State:8004
    rp0428 Guru
    Currently Being Moderated
    >
    Oracle = oracle.jdbc.driver.OracleDriver

    Java Database Driver:
    Oracle ojdbc14.jar
    >
    That is an ancient JDBC jar file and is meant to be used with Java 1.4. Are you really using such as old version of Java?

    You should be using 1.6 and the ojdbc6.jar file.

    The driver used now is oracle.jdbc.OracleDriver
    >
    Could not conect to database:oracle.jdbc.driver.Oracle Driver jdbc:oracle:thin:@ora-svc-NRP45P-APP.extend.local:1521:NRP45P_APP.extend.local
    >
    Is that the server name exactly as it exists in the HOSTS file?

    And is that the database sid or service_name exactly as listed by lsnrctl status?
    >
    But how did sql plus and sql developer bypassed the proxy and firewall?
    >
    My question was whether you are using a firewall or proxy.
  • 5. Re: ODBC connection ORA-12154 Sql State:8004
    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.
    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/
  • 6. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    Hi Rp,

    I am using Java version 1.7.0_05.

    Yes there is a firewall for Domain and public.

    Where can I find teh hosts file or is thsi teh parameter in tnsnames?

    Shoudl I install Java1.4 instead?
  • 7. Re: ODBC connection ORA-12154 Sql State:8004
    sb92075 Guru
    Currently Being Moderated
    CrackerJack wrote:
    Hi Rp,

    I am using Java version 1.7.0_05.

    Yes there is a firewall for Domain and public.

    Where can I find teh hosts file or is thsi teh parameter in tnsnames?

    Shoudl I install Java1.4 instead?
    jdbc:thin does NOT use tnsnames.ora or any file related to SQL*Net.
  • 8. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    Thanks sb.

    Have thsi host file at C:\Windows\System32\drivers\etc

    What do I need to add here?

    # Copyright (c) 1993-2009 Microsoft Corp.
    #
    # This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
    #
    # This file contains the mappings of IP addresses to host names. Each
    # entry should be kept on an individual line. The IP address should
    # be placed in the first column followed by the corresponding host name.
    # The IP address and the host name should be separated by at least one
    # space.
    #
    # Additionally, comments (such as these) may be inserted on individual
    # lines or following the machine name denoted by a '#' symbol.
    #
    # For example:
    #
    # 102.54.94.97 rhino.acme.com # source server
    # 38.25.63.10 x.acme.com # x client host

    # localhost name resolution is handled within DNS itself.
    #     127.0.0.1 localhost
    #     ::1 localhost
  • 9. Re: ODBC connection ORA-12154 Sql State:8004
    sb92075 Guru
    Currently Being Moderated
    CrackerJack wrote:
    Thanks sb.

    Have thsi host file at C:\Windows\System32\drivers\etc

    What do I need to add here?

    # Copyright (c) 1993-2009 Microsoft Corp.
    #
    # This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
    #
    # This file contains the mappings of IP addresses to host names. Each
    # entry should be kept on an individual line. The IP address should
    # be placed in the first column followed by the corresponding host name.
    # The IP address and the host name should be separated by at least one
    # space.
    #
    # Additionally, comments (such as these) may be inserted on individual
    # lines or following the machine name denoted by a '#' symbol.
    #
    # For example:
    #
    # 102.54.94.97 rhino.acme.com # source server
    # 38.25.63.10 x.acme.com # x client host

    # localhost name resolution is handled within DNS itself.
    #     127.0.0.1 localhost
    #     ::1 localhost
    host file contains nothing but comments!
    So what is actual hostname & IP#?

    Somebody needs to decide which protocol, ODBC or JDBC, is actually being used to connect to the Oracle DB.

    below is a working example of how to connect to Oracle DB from standalone Java code.
    bcm@bcm-laptop:~$ cat Conn.java
    import java.sql.*;
    class Conn {
      public static void main (String[] args) throws Exception
      {
       Class.forName ("oracle.jdbc.OracleDriver");
    
       Connection conn = DriverManager.getConnection
         ("jdbc:oracle:thin:@//localhost:1521/v112", "scott", "tiger");
                            // @//machineName:port/SID,   userid,  password
       try {
         Statement stmt = conn.createStatement();
         try {
           ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
           try {
             while (rset.next())
               System.out.println (rset.getString(1));   // Print col 1
           } 
           finally {
              try { rset.close(); } catch (Exception ignore) {}
           }
         } 
         finally {
           try { stmt.close(); } catch (Exception ignore) {}
         }
       } 
       finally {
         try { conn.close(); } catch (Exception ignore) {}
       }
      }
    }
    bcm@bcm-laptop:~$ javac Conn.java
    bcm@bcm-laptop:~$ export CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/owb/wf/lib/ojdbc14.jar:.
    bcm@bcm-laptop:~$ java Conn
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE     11.2.0.1.0     Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    bcm@bcm-laptop:~$
  • 10. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    Thanks sb .

    I have just pinged the server

    Microsoft Windows [Version 6.1.7600]
    Copyright (c) 2009 Microsoft Corporation. All rights reserved.

    C:\>ping ora-svc-NRP45P-APP.extend.local

    Pinging orap2-scan.extend.local [192.165.185.15] with 32 bytes of data:
    Reply from *192.165.185.15*: bytes=32 time=14ms TTL=57
    Reply from 192.165.185.15: bytes=32 time=14ms TTL=57
    Reply from 192.165.185.15: bytes=32 time=14ms TTL=57
    Reply from 192.165.185.15: bytes=32 time=14ms TTL=57

    Ping statistics for 192.165.185.15:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
    Minimum = 14ms, Maximum = 14ms, Average = 14ms

    Can you let me know what exactly to input in the HOSTS file.

    Many Thanks
  • 11. Re: ODBC connection ORA-12154 Sql State:8004
    sb92075 Guru
    Currently Being Moderated
    CrackerJack wrote:
    Thanks sb .

    I have just pinged the server

    Microsoft Windows [Version 6.1.7600]
    Copyright (c) 2009 Microsoft Corporation. All rights reserved.

    C:\>ping ora-svc-NRP45P-APP.extend.local

    Pinging orap2-scan.extend.local [192.165.185.15] with 32 bytes of data:
    Reply from *192.165.185.15*: bytes=32 time=14ms TTL=57
    Reply from 192.165.185.15: bytes=32 time=14ms TTL=57
    Reply from 192.165.185.15: bytes=32 time=14ms TTL=57
    Reply from 192.165.185.15: bytes=32 time=14ms TTL=57

    Ping statistics for 192.165.185.15:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
    Minimum = 14ms, Maximum = 14ms, Average = 14ms

    Can you let me know what exactly to input in the HOSTS file.

    Many Thanks
    how did "ping ora-svc-NRP45P-APP.extend.local" succeed with hostname to IP# resolution?
  • 12. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    Sorry sb I am not sure what to input in. I though pininging it will get the IP address.

    Thanks.
  • 13. Re: ODBC connection ORA-12154 Sql State:8004
    sb92075 Guru
    Currently Being Moderated
    CrackerJack wrote:
    Sorry sb I am not sure what to input in. I though pininging it will get the IP address.

    Thanks.
    how does name become IP#?
  • 14. Re: ODBC connection ORA-12154 Sql State:8004
    CrackerJack Newbie
    Currently Being Moderated
    The DBA have just advised that we do not use local host files but we uses a DNS server where we create the alias ora-svc-NRP45P-APP.extend.local.
    Therefore we don't have to configure the host file.

    What does this mean?

    Sorry sb I am not good at networking at all.Do you get it from ipgconfig /all?
1 2 Previous Next

Legend

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