1 2 3 4 Previous Next 53 Replies Latest reply: Aug 28, 2012 6:05 AM by insanepi RSS

    connecting to Oracle with Instant Client

      I just installed Oracle Instant Client on a machine that has no Oracle client on it. I would like to now hook up to a remote Oracle database, either using SQLPlus or Visual Basic and ODBC. At the c prompt I've tried various logins such as 'sqlplus userid/pswd@database.company.com:1521' or 'sqlplus userid/pswd@database:1521/sid.company.com' can't seem to get the syntax right. sqlplus begins to run but then 'TNS:could not resolve the connect identifier' appears

      I copied over the tnsnames file from a machine with client, and pointed a tns_admin variable to it, no luck.
      I've tried using the IP address and the url with VB connection strings, no luck. conn.Open "provider=msdaora;data source=//host:port/instance", "userid", "pswd"

      Any help with the syntax of either of these would be much appreciated, an example would be great too! Thanks in advance.

        • 1. Re: connecting to Oracle with Instant Client

          Try 'sqlplus userid/pwd@database.company.com:1521/sid'. Let me know how this works for you.
          • 2. Re: connecting to Oracle with Instant Client
            Thanks for your response. Unfortunately that didn't work either. I am able to ping the machine using the IP address, and if I load the full Oracle Client on I can get to Oracle no problem. Instant client errors: ERROR:ORA-12514: TNS:listener does not currently know of service requested in connect descriptor or ERROR:ORA-12705: invalid or unknown NLS parameter value specified. Any other ideas?
            • 3. Re: connecting to Oracle with Instant Client
              For the ORA-12514, that suggests that you are connecting to the correct machine, but you do not have TCP connections turned on by the TNS listener. Please try that and let me know if it works.
              • 4. Re: connecting to Oracle with Instant Client
                With Instant Client install you can use the same connect
                string as with your successful full client installation.
                Make sure you have the tnsnames.ora and maybe sqlnet.ora
                files in a directory pointed to by TNS_ADMIN.

                See http://otn.oracle.com/docs/tech/sql_plus/10102/readme_ic.htm

                -- CJ
                • 5. Re: connecting to Oracle with Instant Client

                  Thanks for your help. I've managed to get the Instant Client sqlplus syntax right, so that is working fine now. Still haven't gotten the Visual basic/ADO string down yet.

                  • 6. Re: connecting to Oracle with Instant Client

                    I'm facing just the same issue (see Instant Client problem on RedHat with tnsnames.ora but it seems as if I was able to find a working solution!

                    Debian Linux:
                    sqlplus usr/pwd@//host:port/sid =-> OK!
                    sqlplus usr/pwd@host:port/sid =-> OK!
                    sqlplus usr/pwd@sid =-> ERROR!

                    wine sqlplus usr/pwd@//host:port/sid =-> OK!
                    wine sqlplus usr/pwd@host:port/sid =-> OK!
                    wine sqlplus usr/pwd@sid =-> ERROR!
                    wine ODBCAD32:
                    Data Source Name: dsn
                    Description: <any>
                    TNS Service Name: host:port/sid
                    User ID: <empty>
                    Test Connection =-> OK!
                    Service Name: host:port/sid
                    User Name: usr
                    Password: pwd
                    wine ODBCTest
                    Connect =-> OK!
                    DSN: dsn
                    User: usr
                    Password: pwd
                    Statement: select * from user_views; =-> OK!

                    - Martin
                    • 7. Re: connecting to Oracle with Instant Client
                      You may want to see the thread you mention in your post for the problem. It may be related to line-terminators not being right. Check line-terminators on your tnsnames.ora file. Let us know how it works out for you!
                      • 8. Re: connecting to Oracle with Instant Client
                        Thanks for all your help. I can now connect to Oracle through Visual Basic (syntax below) using the InstantClient and the ODBC. I can also connect through SqlPlus.

                        To refine things a bit more, I’d like to be able to connect through ODBC without a tnsnames.ora file if possible. I can currently do this through Sqlplus, using syntax like:

                        C:\instantclient>sqlplus username/password@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=database.company.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=servicename)))

                        Is there any way to connect like that in ODBC, using a connection string? My current string looks like:

                        Dim Connection as ADODB.Connection
                        Set Connection = New ADODB.Connection
                        Connection.open "Driver={Oracle in instantclient};Dbq=database.company.com;Port=1521", username, password

                        This works fine given a tnsnames file put in c:\instantclient\network\admin\tnsnames.ora that references the dbq. But I’d like to do it without the tnsnames.ora file, just to save a step in the installation process.


                        • 9. Re: connecting to Oracle with Instant Client

                          Have you tried the Easy Connect connection string that Martin uses:
                              sqlplus username/passwd@//database.company.com/servicename
                          This is also documented in the SQL*Plus Instant Client release notes I
                          linked to previously.

                          A couple of times recently I've seen users type with the old, full
                          connect syntax when it is no longer needed. Is the Oracle
                          documentation poorly presented? (I'm keen to know the answer so any
                          problems can be rectified)

                          I don't have an answer for the ODBC issue: I haven't played with ODBC

                          -- CJ
                          • 10. Re: connecting to Oracle with Instant Client

                            Hi CJ, thanks for the reply. Sorry I have been away from that project for a while.

                            I have tried Easy Connect, unsuccessfully.
                            This does not work:
                            sqlplus username/passwd@//database.company.com/servicename

                            This works:
                            sqlplus username/password@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST

                            I do think the Oracle documentation is difficult to follow. What would be ideal for me is a brief example, written in Visual Basic, that connects to an Oracle database, with as little needed on the client-side as possible - no tnsnames.ora file, just a connection string similar to your easy connect that can read and write to the database. I think this is a very common thing for programmers to do, and would be helpful to many.


                            Steve Gaughan
                            • 11. Re: connecting to Oracle with Instant Client
                              Hi Steve,

                              Did you ever resolve this issue. I too can get the "verbose" connection string to work. But every other method fails.

                              • 12. Re: connecting to Oracle with Instant Client
                                Steve and Mark,
                                Try using the syntax:


                                Let us know if that works for you.
                                • 13. Re: connecting to Oracle with Instant Client
                                  The syntax that worked for me is account/password@mybox.abcd.com:portnum/mydb.xxx.yyy

                                  Depending on how DNS is configured on your client, you might not need to spell out the fully qualified "mybox.abcd.com" name and might get away with just the "mybox" prefix.

                                  However, the key thing for me was that instant client was NOT looking for the SID (which in this example is "mydb"). Rather, it was looking for the GLOBAL_NAME which is "mydb.xxx.yyy". TNSPING does not show that (assuming you have TNSPING installed)! I had to look in TNSNAMES.ORA to see what the GLOBAL_NAME was for my database. It's not necessarily the same as the SID.

                                  Also, you must know the port the listener is on. You can get that from TNSNAMES, as well. If you have a server that runs lots of listeners on various ports it would be nice if they let us specify a range of ports to interrogate to find the GLOABL_NAME we're looking for.

                                  It's an irony that this new client finally frees us from the hassles of properly maintaining and distributing TNSNAMES.ORA, but it could mean constant changes on the client side every time a dba moves a database or a listener. DNS can help us find the server box, but having to know the exact port number is a drag.

                                  I hope this helps. This has been a very confusing aspect of the instant client.


                                  example tnsnames.ora entry for SID "mydb": (I apologize if this discussion group software undents it but you can still read it)

                                  mydb =
                                  (DESCRIPTION =
                                  (ADDRESS_LIST =
                                  (ADDRESS =
                                  (COMMUNITY = tcp.abcd.com)
                                  (PROTOCOL = TCP)
                                  (Host = mybox.abcd.com)
                                  (Port = 1523)
                                  (CONNECT_DATA =
                                  (SID = mydb)
                                  (GLOBAL_NAME = mydb.xxx.yyy)
                                  • 14. Re: connecting to Oracle with Instant Client
                                    Thanks for the detail. As you've found out, you need to use the
                                    service name, not the SID.
                                    I hope this helps. This has been a very confusing aspect
                                    of the instant client.
                                    Sounds like an enhancement request for the OCI documentation!

                                    With Instant Client you can still use the old TNSADMIN variable
                                    which makes it easy to use a tnsnames.ora file.

                                    There is some discussion of all this in the sqlplus note on the Instant
                                    Client OTN page. The Oracle Net manuals have full detail on the Easy
                                    Connect syntax.

                                    -- CJ
                                    1 2 3 4 Previous Next