1 2 3 4 Previous Next 53 Replies Latest reply: Aug 28, 2012 6:05 AM by insanepi Go to original post RSS
      • 15. Re: connecting to Oracle with Instant Client
        3004
        I am trying to use IC to connect an application that would otherwise use Oracle Client. If I understand what's needed, I must first install the IC product in a directory, in my case I used c:\program files\InstantOracleClient. So that I could use the SQLPlus program, I put that in this same directory. Next, I added this directory to the end of the system environment PATH variable. I also created two new system environment variables TNS_ADMIN and a SQLPath, which I also set to this directory. Lastly, I placed a copy of the TNSNAMES.ora file into this directory. Is this all that's required to make this thing work? Or am I missing something?

        Thank you,
        Howard Brown
        • 16. Re: connecting to Oracle with Instant Client
          352290
          Howard,
          Yes, that is all that is required. However, you should probably put the directory AT THE FRONT of the path. You do NOT need the SQLPath environment variable as well. I hope that helps!

          Toliver
          • 17. Re: connecting to Oracle with Instant Client
            3004
            For me this connection string work:

            [oledb]
            ; Everything after this line is an OLE DB initstring
            Provider=MSDASQL.1;Password=SECRET;Persist Security Info=True;User ID=USERNAME;Extended Properties="Driver={Oracle in instantclient};Dbq=127.0.0.1:1521/SERVICENAMEHERE"

            VBS test script (uses MS XML 4 to show recordset output):

            Dim Connection' as ADODB.Connection
            Dim Recordset'as ADODB. Recordset
            Dim blnTest
            Dim strQUERY

            Set Connection = CreateObject("ADODB.Connection")

            Set Recordset = CreateObject("ADODB.Recordset")


            blnTest = Connection.Open("Driver={Oracle in instantclient};Dbq=127.0.0.1:1521/SERVICENAMEHERE", "USERNAME", "PASSWORD")

            strQUERY = "SELECT * FROM (SELECT * FROM TABLENAMEHERE) WHERE ROWNUM < 5;"

            Recordset.Open strQUERY, Connection, 3, 1, &H0001

            MsgBox (ADORS2XML(Recordset))
            MsgBox (blnTest)
            MsgBox Connection.State

            Recordset.Close
            Connection.Close

            MsgBox Connection.State

            Set Connection = Nothing
            Set Recordset = Nothing


            Function ADORS2XML(ADORS)
            Dim strXML
            Dim objXMLDOM_XML
            Set objXMLDOM_XML = CreateObject("MSXML2.DOMDocument.4.0")
            ADORS.Save objXMLDOM_XML, 1
            ADORS2XML = objXMLDOM_XML.xml
            Set objXMLDOM_XML = Nothing
            End Function
            • 18. Re: connecting to Oracle with Instant Client
              3004
              hi I am not able to solve the problem while i am trying to connect to ORacle 8i server through ADO,I am getting the Error Message that TNS could not resolve the servce name.the connecting string i am using is
              Con.Open "Provider=MSDAORA;Data Source=servername;User ID=TEST1; Password=TEST;DBQ=HostString;PORT=1521"
              pleae help me out immediately
              • 19. Re: connecting to Oracle with Instant Client
                3004

                I am unsuccessfully trying to connect to remote Oracle database using OleDB/ODBC/Oracle Client.
                Do I have to install Oracle Client in order to use those drivers and which version?
                I would really appreciate any help. THANK YOU!
                • 20. Re: connecting to Oracle with Instant Client
                  3004
                  Connection strings can be a real pain, but this thread helped me...took some trial and error.

                  My story:
                  - .NET webapp, running on WinXP, IIS 5.0
                  - need ADO.NET connection
                  - do NOT want to rely on tnsnames.ora
                  - do NOT want to use instant client (or standard client)
                  - use Microsoft OLE DB Provider for Oracle (MSORCL32.DLL)
                  - MDAC 2.7, believe this is where MS driver comes from

                  This connection string works:
                  PROVIDER=msdaora;DATA SOURCE=HOST:PORT/SID;USER ID=UID;PASSWORD=PWD

                  Be sure to replace HOST, PORT, SID, UID, and PWD with meaningful values. For host you can use either an IP address or hostname. If you omit ":PORT" it appears to default to using port 1521.

                  Hope this helps.

                  Evan
                  • 21. Re: connecting to Oracle with Instant Client
                    3004
                    I am trying following Connection String but i am getting Error "OleDbException: ORA-12560: TNS :protocol adapter error "

                    connString="Provider=OraOLEDB.Oracle;DataSource=192.168.0.7:1521/mydb1;User ID=system;Password=manager;";

                    Can somebody tell me whats wrong with it.

                    When i try to access same thing using VBS script then it works okay.

                    Thankx in advance
                    • 22. Re: connecting to Oracle with Instant Client
                      3004
                      How did you resolve the 12705 NLS error?
                      • 23. Re: connecting to Oracle with Instant Client
                        547541
                        am trying following Connection String but i am getting Error "OleDbException: ORA-12560: TNS :protocol adapter error "

                        connString="Provider=OraOLEDB.Oracle;DataSource=192.168.0.7:1521/mydb1;User ID=system;Password=manager;";

                        Can somebody tell me whats wrong with it.
                        -----------------------------------------------------------------------------------------------
                        I meet problem as u.:(
                        • 24. Re: connecting to Oracle with Instant Client
                          Eduardo Legatti
                          Hi,

                          Did you installed SQL*PLUS, in order test the native connection with Oracle ?
                          e.g: sqlplus system/manager@192.168.0.7:1521/mydb1
                          Did you configured the NLS_LANG variable on the registry ?

                          Cheers
                          • 25. Re: connecting to Oracle with Instant Client
                            470072
                            Hi All,
                            I'm having issues connecting to a 10G R2 oracle server using Instant Client. I can connect using this format:

                            sqlplus hr/<password>@CentOSServ6:1521/orcl

                            However I can't seem to get the tnsnames.ora correct. Correct me if I'm wrong, but if I can connect using the above method then I should be able to connect using the tnsnames.ora file. I've set the TNS_ADMIN, LD_LIBRARY_PATH, SQLPATH and PATH to all include or point and to the directory containing the IC files and the tnsnames.ora file. Everything is in one directory.

                            My tnsnames.ora looks like:

                            ORCL =
                            (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = CentOSServ6)(PORT = 1521))
                            (CONNECT_DATA =
                            (SERVER = DEDICATED)
                            (SERVICE_NAME = orcl)
                            )
                            )

                            When I try sqlplus hr/<password>@ORCL I get "TNS:could not resolve the connect identifier specified"

                            Any ideas?

                            Thanks,
                            Mike
                            • 26. Re: connecting to Oracle with Instant Client
                              Eduardo Legatti
                              Hi,

                              Try put this below on your SQLNET.ORA file:
                              NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
                              If not work, try create a service on TNSNAMES.ORA file like below:
                              ORCL = "CentOSServ6:1521/orcl"
                              Cheers
                              • 27. Re: connecting to Oracle with Instant Client
                                470072
                                I didn't have a SQLNET.ORA file.. do you mean place that in the file? Does case matter on a UNIX system for the filename and contents?

                                For the tnsnames.ora file... do you mean add that line into it? Should I remove the already defined ORCL =.... in it?

                                I've got this feeling that my problem maybe that it's just not seeing my tnsnames.ora file...

                                Here's my .profile file

                                DYLD_LIBRARY_PATH=/Library/instantclient10_1:$DYLD_LIBRARY_PATH
                                LD_LIBRARY_PATH=/Library/instantclient10_1:$LD_LIBRARY_PATH
                                SQLPATH=/Library/instantclient10_1
                                TNS_ADMIN=/Library/instantclient10_1
                                PATH=/Library/instantclient10_1:$PATH
                                export DYLD_LIBRARY_PATH
                                export LD_LIBRARY
                                export SQLPATH
                                export TNS_ADMIN
                                export PATH

                                I did try creating the sqlnet.ora file in the instantclient10_1 dir and placing "NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)" in it. Still no luck.

                                Thanks,
                                Mike
                                • 28. Re: connecting to Oracle with Instant Client
                                  Eduardo Legatti
                                  Hi,

                                  And about $ORACLE_HOME variable?
                                  You can try too create a TWO_TASK variable with value like below:
                                  export TWO_TASK=ORCL
                                  Cheers
                                  • 29. Re: connecting to Oracle with Instant Client
                                    LaurenzAlbe
                                    Coppage, don't let yourself get confused. Legatti seems to be guessing.

                                    - You don't need an ORACLE_HOME environment variable.
                                    - You don't need a TWO_TASK environment variable.
                                    - sqlnet.ora and tnsnames.ora (both file names in lower case!) must be in the directory to which TNS_ADMIN points (in your case, /Library/instantclient10_1)
                                    - as Legatti suggested, your sqlnet.ora must contain NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
                                    - A correctly set NLS_LANG environment variable is not essential to establish a connection, but necessary for the actual work.

                                    If all that is correct and you still get the error when you try to connect, generate a client trace to find out what goes awry:

                                    In sqlnet.ora, set TRACE_LEVEL_CLIENT=SUPPORT and TRACE_DIRECTORY_CLIENT=<a directory>, then you should get a trace file in that directory when you try to connect. This trace file will tell you what is amiss.

                                    Yours,
                                    Laurenz Albe