1 2 Previous Next 15 Replies Latest reply: Nov 1, 2012 5:23 PM by CrackerJack RSS

    ODBC connection ORA-12154 Sql State:8004

    CrackerJack
      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
          >
          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
            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
              But how did sql plus and sql developer bypassed the proxy and firewall?
              • 4. Re: ODBC connection ORA-12154 Sql State:8004
                rp0428
                >
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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