Connecting to Oracle from SQL

  • So I downloaded the lite, and I see no way to install this ... I'm hunting online for some type of documentation, but thus far no luck. How the heck do I set this up?

  • I dont think Oracle lite will work. You will need to download Oracle client. I have provided a link to download Oracle 10G client.

    BUT like Bru has suggested, first you need to know what version of Oracle are you running on the server. and then go to download the correct Oracle client you will need. It could 9,10 or 11.

    Second option on that page is "Oracle Database 10g Client Release 2 (10.2.0.1.0)". Again please confirm the version you will need.

    Oracle Client 10G

  • We're running 10g, but wouldn't the 11g be backward compatible? All I need is the client and this is what I'm trying now: http://www.oracle.com/technology/software/products/database/oracle11g/111060_win32soft.html

  • Not an Oracle expert. But it probably will work.

    I have used 10g clients against Oracle 9 without any issues.

    You have the correct link to download Oracle 11 client.

  • vstitte (1/6/2010)


    Not an Oracle expert. But it probably will work.

    I have used 10g clients against Oracle 9 without any issues.

    You have the correct link to download Oracle 11 client.

    That is not correct it is a known issue that 10g blocks 9i connection something that have made me call Oracle metalink support.

    The only time to use a different version of Oracle is when you are connecting to 8i because in Windows Oracle wants 9i RC2 and above for development.

    Kind regards,
    Gift Peddie

  • Hello GP,

    Thanks for pointing this out. This may be an issue for sure.

    But I definitely have worked in an environment where we have used 10g client against Oracle 9, but I am not an expert and we had a really good DBA who set it up.

    I still would prefer to use the same client version if I can.

    Thanks

  • Thanks for pointing this out. This may be an issue for sure.

    But I definitely have worked in an environment where we have used 10g client against Oracle 9, but I am not an expert and we had a really good DBA who set it up.

    I am the Oracle break fix resource in most places I have worked so I don't understand what the above means.

    I still would prefer to use the same client version if I can.

    And all Oracle clients are at Oracle site because I think you can use 8i client if you are in Java and not .NET.

    Kind regards,
    Gift Peddie

  • Hey all,

    Never before have I felt so dumb trying to do my job, but I'm still struggling on this. So I went through the 11g client install and installed only the Oracle Client 11.1.0.6.0 with nothing else enabled.

    When I open a DTS that has an Oracle connection, I get an error stating that "no provider exists for this connection on this computer."

    Did I not install something I need? Do I need something under the Oracle Windows Interfaces?

    Also, I put the sqlnet.ora and tnsnames.ora files from the old environment into my network\admin folder. When I tnsping one of the servers, i get:

    Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage

    3512 not found; No message file for product=NETWORK, facility=TNSAttempting to

    contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = xxx.yyyy.com) (PORT = 1566))) (CONNECT_DATA = (SID = prod)))

    Message 3509 not found; No message file for product=NETWORK, facility=TNS

    I checked and I have no environment variable for my HOME. So I did a set ORACLE_HOME=C:\Oracle\product\11.1.0\client_1. Still same error on the tnsping.

    Thanks again

  • GP/All,

    Apologze for the confusion. The issue that Adam is having really doesnt involve Oracle 9. My post may have led us down the wrond path.

    I just was stating on something about backward compatibility that Adam was asking. He is planning on installing Oracle 11 client to access Oracle 10 database.

    And secondly, all I was trying to say is, if the Oracle database is on 10g, then I would recommend using Oracle client 10g to connect to the database. Thats all.

    Again, I am not an Oracle expert and would like to apologize for any confusion.

  • We were able to resolve the tnsping problem by copying the network\mesg folder from another user. For some reason, my install only had the nlus.msb file. So the ping works, but still getting the same error when opening a DTS connection. I went back and installed the OLE provider in the client install, still fails.

    Haven't tried using sqlplus to connect to an Oracle environment yet, will do so a bit later after a meeting. Any thoughts though on the whole DTS problem? Seems like I'm still missing the proper driver?

    Thanks again

  • Ok, so I can connect just fine via SQLPLUS ... still at a loss here.

    I checked the old environment (SQL2000) and see that there is a oraoledb.oracle provider in the linked servers and I do not have one on my local (SQL2005 and 2008) where I went through this Oracle client install.

    After checking online for this oraoledb.oracle download, it took me here: http://www.oracle.com/technology/software/tech/windows/ole_db/index.html, which confused me even more as the client install did have these in the install and I installed the ole db.

    So on this old environment, I found a linked server and I recreated the same linked server locally (OLE DB Provider for Oracle), and I created it just fine locally. The problem is though when querying the linked server, no matter the table/view, I get an error similiar to this:

    The OLE DB provider "MSDAORA" for linked server "test" supplied inconsistent metadata for a column. The column "XXX" (compile-time ordinal 10) of object ""XXX"."YYY"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

    Sounds like an incompatibility with SQL and Oracle? I can't retrieve this data?

    So at least it seems as though I can connect via OLE DB Provider for Oracle in a linked server ... so why is it that I still can't open an Oracle DTS step?

    Please help, I'm ready to punch someone ...

  • Hi Adam,

    Was it the full client you installed? If so, the OLE DB driver is in the 'Oracle Windows Interfaces' component, which is not automatically checked for installation IIRC.

    It sounds like you have maybe installed the 11g full client without the OLE DB driver, then installed a 10g OLE DB driver. These are in separate Oracle 'homes' (installations) which is causing the message path issues, and probably also version-related problems.

    I would de-install the independant OLE DB driver, then use the Universal Installer to add the 11g driver to your existing client installation.

    The messages are purely informational - you can look them up here http://www.ora-code.com/code/TNS-8.html - but they are just these two lines:

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION= ...

    HTH

  • Thanks Nice.

    So I started over at this point. I uninstalled and removed. I did a fresh install and installed the following:

    - Custom

    - C:\Oracle

    - Bypassed windows 7 errors

    - Oracle Client 11.1.0.6.0

    -- Oracle Windows Interfaces 11.1.0.6.0

    --- Oracle Provider for OLE DB 11.1.0.6.0

    The mesg folder still only contained the nlus.msb file. Still can not open DTS steps which have an Oracle connection. This time though I have a completely different error ... "OraOLEDBplus11.dll: The specified module could not be found." Followed by BindMoniker failed " a dynamic link library (DLL) initialization routine failed." Followed by a package error "class not registered."

    Am I missing something on the install? Perhaps something with Windows 7? I'm going to give it a shot on a virtual XP ...

  • Adam Bean (1/12/2010)


    Thanks Nice.

    So I started over at this point. I uninstalled and removed. I did a fresh install and installed the following:

    - Custom

    - C:\Oracle

    - Bypassed windows 7 errors

    - Oracle Client 11.1.0.6.0

    -- Oracle Windows Interfaces 11.1.0.6.0

    --- Oracle Provider for OLE DB 11.1.0.6.0

    The mesg folder still only contained the nlus.msb file. Still can not open DTS steps which have an Oracle connection. This time though I have a completely different error ... "OraOLEDBplus11.dll: The specified module could not be found." Followed by BindMoniker failed " a dynamic link library (DLL) initialization routine failed." Followed by a package error "class not registered."

    Am I missing something on the install? Perhaps something with Windows 7? I'm going to give it a shot on a virtual XP ...

    If you had posted you are running Windows 7 this conversation would have ended because DTS is SQL Server 2000 Windows 7 cannot run it so you need to install your 1.7 gig Oracle client in an XP box. Then install both SQL Server 2000 and 2008 to do the upgrade and then connect to Oracle.

    Kind regards,
    Gift Peddie

  • Gift, I can run DTS on Windows 7 using SSMS (2k5 and 2k8) just fine.

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply