This discussion is archived
1 2 3 4 Previous Next 53 Replies Latest reply: Aug 28, 2012 4:05 AM by 958430 RSS

connecting to Oracle with Instant Client

3004 Newbie
Currently Being Moderated
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.

Steve
  • 1. Re: connecting to Oracle with Instant Client
    352290 Newbie
    Currently Being Moderated
    Steve,

    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
    3004 Newbie
    Currently Being Moderated
    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
    352290 Newbie
    Currently Being Moderated
    Steve,
    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
    3047 Oracle ACE
    Currently Being Moderated
    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
    3004 Newbie
    Currently Being Moderated

    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.

    Steve
  • 6. Re: connecting to Oracle with Instant Client
    418137 Newbie
    Currently Being Moderated
    Hello,

    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!

    CrossOver/Wine/Windows:
    -----------------------
    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
    352290 Newbie
    Currently Being Moderated
    Martin,
    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
    3004 Newbie
    Currently Being Moderated
    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.

    Thanks,

    Steve
  • 9. Re: connecting to Oracle with Instant Client
    3047 Oracle ACE
    Currently Being Moderated
    Steve,

    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
    recently.

    -- CJ
  • 10. Re: connecting to Oracle with Instant Client
    3004 Newbie
    Currently Being Moderated

    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
    database.company.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=servicename)))

    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.

    Thanks,

    Steve Gaughan
  • 11. Re: connecting to Oracle with Instant Client
    3004 Newbie
    Currently Being Moderated
    Hi Steve,

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

    Mark
  • 12. Re: connecting to Oracle with Instant Client
    352290 Newbie
    Currently Being Moderated
    Steve and Mark,
    Try using the syntax:

    username/password@company.com/SID:port

    Let us know if that works for you.
  • 13. Re: connecting to Oracle with Instant Client
    3004 Newbie
    Currently Being Moderated
    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.

    Keith

    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
    3047 Oracle ACE
    Currently Being Moderated
    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