Connect to Oracle DB from VB 2008 application without installing Oracle software?

Question

Imports System.Data.OleDb

Public Class Log

    Private mConnectionString As String = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))(CONNECT_DATA=(SID=xxx)(SERVER=DEDICATED)));User Id=xxx;Password=xxx;"
    Dim ds As New DataSet
    Dim da As New OleDbDataAdapter
    Dim dr As DataRow
    Dim Connection As New OleDbConnection(mConnectionString)
    Dim Command As OleDbCommand
    goes on...

That's the code, and it works great on our development machines. We all have the Oracle providers installed on our machines. Now I tried using this code in an app on another machine that does not have the Oracle software installed and it doesn't work.

Now I know I can install the Oracle providers on these other machines and it will work. Problem with that is A) there are many of them and B) I'd have to go through our IT department and it would take six months for them to do it. So my question is, can I connect to this Oracle database from a machine without the Oracle providers installed? I thought Microsoft had it's own Oracle provider but it doesn't show up under System.Data. The .NET version is 3.5 if that helps. Any ideas?

Answer 1

I'm pretty certain you have to install the Oracle provider in order for this to work. OleDb can easily connect to SQL Server and Access and so on, but only because these providers come pre-installed in Windows.

I think your only option (other than installing the Oracle provider on every machine) is to create a SQL Server "front" database that includes pass-through tables to each Oracle table you need, and then get your data from SQL Server instead of Oracle.

Actually, another option would be to have your client applications get their data from an intermediate web service instead of connecting directly to the database, but this would probably entail a major re-write.

Answer 2

Would it be possible to find and include the Oracle DLLs with your app instead of installing them?

Answer 3

The problem is actually you started a project without discussing the infrastructure needs with your company's IT/DBA team. This is not a technical problem but a process problem.

That being said here is a possible solution (although I haven't used personally). http://devart.com/dotconnect/oracle/

Answer 4

I've had fairly good luck with the Oracle Instant Client and ODP.NET, which is pretty much a straight XCOPY deploy (if you don't need ODBC).

IIRC, you do need to modify the PATH environment variable, but that's relatively painless - especially compared to the hoops Oracle used to make you jump through.