Which data provider should I use for accessing oracle 8i?

[candice]

Hello guys:

I'm now using oracle 8i database system. And I wanna access it through VB.net 2005. I've checked out that ODP.net (oracle data provider), ODBC and OLE DB all can be used to achieve it.
But when I read the ODP.net FAQ, it shows that:
Q: Can ODP.NET work with Oracle8, Oracle8i, Oracle9i, and Oracle10g database (DB) servers? Can these DB servers be on Linux or Unix?
A: Yes and yes. Oracle clients can support older and newer versions of the Oracle database. Oracle clients on Windows can work with Oracle database servers on any platform.

Q: Can ODP.NET be used with an Oracle8, Oracle8i, or Oracle9i Release 1 client?
A: No. You need to use the Oracle9i Release 2 or higher client.

I'm not very sure what's the difference between a database server and client... And seems ODP.net is so popular that besides it I can not find out any other appropriate way to enable the use of ADO.net.(seems ODBC and OLE DB can, but they are not wildly used on oracle.)
Could anybody give me some advise?
Any information will be very appriciated!
Thanks

 

[Arg81]

Database server is where your database is stored - so where-ever you install your Oracle 8i database, and a client is a device that accesses this server to access it's data.

The first answer is telling you that the Oracle client installed on any of your Windows PC's can access your Oracle database server, be it on Windows Server, Linux or Unix via ODP.net.

The second answer then goes on to tell you that the Oracle client that needs to be installed on your Windows PC(s) needs to be at least Release 2 version to be able to use ODP.net to access the server.

HTH
Luke

 

[cjard]

Use Microsoft's System.Data.OracleClient - that way ODP.Net doesnt need installing on all your client machines.

Unzip and watch the attached WMV

I demonstrate using the server explorer to add a new connection to an oracle instance. My machine also has ODP.net installed which adds the Oracle Explorer tab - this lets me edit oracle tables etc, but i DONT use ODP for accessing oracle - As you will see I use the default Microsoft Oracle client that comes with .NET 2.0

 

[cjard]

Note that you still need the oracle client installed on your local machine!! This is a suite of software that allows you to access Oracle databases - You need either the oracle 9i2 or 10g client. By default these are found in:

C:\ora92
c:\oracle\product\10.x

If your machine has neither of these then you must install the client before you can use the database.. The client is NOT ODP.net!

 

[candice]

Thanks V-B New-B and Cjard,
So there're two ways to connect oracle database with VB.net.
1, use ODP.net
2, use Microsoft's System.Data.OracleClient
And no matter which way is used, oracle 9i2 and later version client must be installed. The previous version can not be used.
If there is oracle explorer in the view menu, it's because of the installation of ODP.net.

Is my conclusion right or not?

 

[Arg81]

you are correct

 

[candice]

Sorry to bother you but there are still some small questions,
1, I find somewhere that oracle client 8i release 3 also can be used as the client when using .net framework data provider. What's the meaning?
2, I'm now using visual studio 2005, do I need to download .net framework data provider on msdn if I wanna use it? Or Has it already included in the VS2005?
3,

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.OracleClient
[/SIZE][SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE][SIZE=2] OracleConnect
[/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Main()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"server = myserver; uid = myuid; password = mypassword;"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Create connection
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleConnection(connString)
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]conn.Open()
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Connection Opened"[/COLOR][/SIZE][SIZE=2])
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Connection Properties"[/COLOR][/SIZE][SIZE=2])
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"- ConnectionString : {0}"[/COLOR][/SIZE][SIZE=2], conn.ConnectionString)
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"- ServerVersion : {0}"[/COLOR][/SIZE][SIZE=2], conn.ServerVersion)
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"- State : {0}"[/COLOR][/SIZE][SIZE=2], conn.State)
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleException
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Error: "[/COLOR][/SIZE][SIZE=2] & ex.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' Close Connection
[/COLOR][/SIZE][SIZE=2]conn.Close()
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Connection Closed"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Module
[/COLOR][/SIZE]

This is the code I want to run.
Following are the conditions:
(1), I've already downloaded Microsoft oracle .net data provider and set it up.
(2), The client I'm using is oracle 8i release 3.
(3), I've added the connection in the sever explorer with my oracle database.
(4), I've already added reference. so the code has no error.
(5), I set the Application type to Console application.

But when I run this program, the DOS-like window come out with nothing on it. Could anybody give me some hint why it happened?
Thanks a lot!

 

[cjard]

Sorry to bother you but there are still some small questions,
1, I find somewhere that oracle client 8i release 3 also can be used as the client when using .net framework data provider. What's the meaning?

The System.Data.OracleClient namespace can work with the 8i3 version of the oracle client software ("database driver") that needs installing on your machine, probably in c:\ora8

2, I'm now using visual studio 2005, do I need to download .net framework data provider on msdn if I wanna use it? Or Has it already included in the VS2005?

All of the System.* namespaces are core parts of every .NET framework installation. This is why i recommend using System.Data.OracleClient because it means that your cleint machines will not need ODP.net installing because they already have a library capable of accessing oracle installed as part of the core framework.
If you develop and use the ODP.net library (regardless of client) you will have to install it on every end machine

3,

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System[/SIZE]
[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data[/SIZE]
[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.OracleClient[/SIZE]
[SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE][SIZE=2] OracleConnect[/SIZE]
[SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Main()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"server = myserver; uid = myuid; password = mypassword;"[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Create connection[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleConnection(connString)[/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2]conn.Open()[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Connection Opened"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Connection Properties"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"- ConnectionString : {0}"[/COLOR][/SIZE][SIZE=2], conn.ConnectionString)[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"- ServerVersion : {0}"[/COLOR][/SIZE][SIZE=2], conn.ServerVersion)[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"- State : {0}"[/COLOR][/SIZE][SIZE=2], conn.State)[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OracleException[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Error: "[/COLOR][/SIZE][SIZE=2] & ex.ToString())[/SIZE]
[SIZE=2][COLOR=#0000ff]Finally[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]' Close Connection[/COLOR][/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Connection Closed"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Module[/COLOR][/SIZE]

This is the code I want to run.
Following are the conditions:
(1), I've already downloaded Microsoft oracle .net data provider and set it up.
(2), The client I'm using is oracle 8i release 3.
(3), I've added the connection in the sever explorer with my oracle database.
(4), I've already added reference. so the code has no error.
(5), I set the Application type to Console application.

But when I run this program, the DOS-like window come out with nothing on it. Could anybody give me some hint why it happened?
Thanks a lot!

1) You cannot download this, it is part of the .net framework
2) ok
3) you do not need to do this
4) ok
5) ok

I have no idea why the console window is blank. Run this code in the debugger instead.

Oh, and when you get tired of writing code like this to do databases, you can take a read of the DW2 link in my signature, and do it the easy (and better engineered) way!

 

[ccraneco]

You can also use Oracle 10g's ODAC which includes your Oracle Data Provider. This ODP can be used to connect to Oracle 8i databases as well as anything above that. I know this for a fact because we are in the process of upgrading our Oracle 8i to 10g and we have a VB.NET application that connects to Oracle 8i. I use this 10g ODAC to connect to the 8i, and also connects to our new 10g server. I am using this 10g ODAC to store CLOBs into Oracle 8i which I am not able to do with any other connector.

 

[cjard]

That;s funny.. we use Microsoft's OracleClient framework in conjustion with the 9i client, to read and write clobs in our oracle 9i (running in 8i) mode database every day..

 

[candice]

Thanks ccraneco and cjard, I tried it on our database.
It works out with Microsoft's OracleClient framework and 8i realease 2 client