Connection to ORACLE: Need some explanations

[tommaso.gastaldi]

Hi I have a question:

I have been connecting to oracle on my own machine successfully by
using either one of the 2 following:
Me.ConnectionString = _
"Provider=OraOLEDB.Oracle" & _
";User ID=" & Me.UserId & _
";Password=" & Me.Password & _
";Data Source=" & Me.ServiceName

Me.ConnectionString = _
"Provider=MSDAORA" & _
";User ID=" & Me.UserId &
_
";Password=" & Me.Password
& _
";Data Source=" &
Me.ServiceName & _
";Persist Security
Info=False"

Now it occurs to me that I have installed (as administrator) the Client
Oracle on a machine which is used in a university class and there are
some restrictions for the users (students).

There happens that if I use Provider=OraOLEDB.Oracle" & ... I receive
an error message that says that "the Oracle Client is not registered
on the local machine".
This happens in any case, regardless whether I log as admnistrator or
as user.

If I use "Provider=MSDAORA" ... the connection works fine.

I cannot explain this behaviour and I would like to have some light
from you. Actually I am not even clear on what is the essential
difference between the 2 ways of connecting, as both need the oracle
client.

Any explanation is much apprecciate.

-tom

 

[Paul Clement]

On 21 Jun 2006 05:16:29 -0700, (e-mail address removed) wrote:

¤ Hi I have a question:
¤
¤ I have been connecting to oracle on my own machine successfully by
¤ using either one of the 2 following:
¤ Me.ConnectionString = _
¤ "Provider=OraOLEDB.Oracle" & _
¤ ";User ID=" & Me.UserId & _
¤ ";Password=" & Me.Password & _
¤ ";Data Source=" & Me.ServiceName
¤
¤ Me.ConnectionString = _
¤ "Provider=MSDAORA" & _
¤ ";User ID=" & Me.UserId &
¤ _
¤ ";Password=" & Me.Password
¤ & _
¤ ";Data Source=" &
¤ Me.ServiceName & _
¤ ";Persist Security
¤ Info=False"
¤
¤ Now it occurs to me that I have installed (as administrator) the Client
¤ Oracle on a machine which is used in a university class and there are
¤ some restrictions for the users (students).
¤
¤ There happens that if I use Provider=OraOLEDB.Oracle" & ... I receive
¤ an error message that says that "the Oracle Client is not registered
¤ on the local machine".
¤ This happens in any case, regardless whether I log as admnistrator or
¤ as user.
¤
¤ If I use "Provider=MSDAORA" ... the connection works fine.
¤
¤ I cannot explain this behaviour and I would like to have some light
¤ from you. Actually I am not even clear on what is the essential
¤ difference between the 2 ways of connecting, as both need the oracle
¤ client.
¤
¤ Any explanation is much apprecciate.

If you can connect with SQL Plus then it's very likely a security issue with respect to .NET. Are
you connecting from a standard Windows application or an ASP.NET app?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[tommaso.gastaldi]

Hi Paul,

It's a standard VB.NET client application.
It just does the connection and allows sending SQL commands.
SQL plus can connect.

If it's a security issue I am not clear why msdaora is not affected
(??)

Any ideas?

-tom

 

[Paul Clement]

On 21 Jun 2006 06:34:23 -0700, (e-mail address removed) wrote:

¤ Hi Paul,
¤
¤ It's a standard VB.NET client application.
¤ It just does the connection and allows sending SQL commands.
¤ SQL plus can connect.
¤
¤ If it's a security issue I am not clear why msdaora is not affected
¤ (??)
¤
¤ Any ideas?

Does the error indicate the Oracle client is not registered or is it referring to the Oracle OLEDB
Provider?

If I remember correctly the Oracle OLEDB Provider is a separate install:

http://www.oracle.com/technology/tech/windows/ole_db/index.html


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[tommaso.gastaldi]

The PC's are for students. There is a copy of the oracle client
installed on each pc.
Clearly there is also a remote machine (SERVER) which has Oracle
server.

Right! Oracle OLEDB Provider is a separate install and it is the one to
which the message is referring to. The msg say thta the OleDb provider
is not registered on the current machine. But actually it is. In fact
we can get connected by using msdaora.

-tom

Paul Clement ha scritto:

 

[Paul Clement]

On 21 Jun 2006 09:09:58 -0700, (e-mail address removed) wrote:

¤ The PC's are for students. There is a copy of the oracle client
¤ installed on each pc.
¤ Clearly there is also a remote machine (SERVER) which has Oracle
¤ server.
¤
¤ Right! Oracle OLEDB Provider is a separate install and it is the one to
¤ which the message is referring to. The msg say thta the OleDb provider
¤ is not registered on the current machine. But actually it is. In fact
¤ we can get connected by using msdaora.
¤

But those are two different OLEDB providers, each from a different vendor. The MSDAORA OleDb
provider is from Microsoft and is usually already installed. The OraOLEDB.Oracle OleDb provider is
from Oracle and is not installed unless someone explicitly installed it.

Try checking the bin subfolder in your Oracle client installation folder. There should be a file
called OraOLEDB.dll in addition to some other OraOLEDB files. If these files are not present then
this provider needs to be installed.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[tommaso.gastaldi]

Hi Paul.

The Oracle client is installed because it has been installed on purpose
for the connection and I have seen it being installed by the
administrator.

Note that the msdaora does not behave like if it were a full client,
infact it does *NOT* work at all if the Oracle Client is not installed.
It just appears to be as a layer of software which talks with the
Oracle client (which is installed).

I dont' really get what the connection string containing msdaora does
different with respect to the other one.

-tom

Paul Clement ha scritto:

 

[Paul Clement]

On 21 Jun 2006 09:09:58 -0700, (e-mail address removed) wrote:

¤ The PC's are for students. There is a copy of the oracle client
¤ installed on each pc.
¤ Clearly there is also a remote machine (SERVER) which has Oracle
¤ server.
¤
¤ Right! Oracle OLEDB Provider is a separate install and it is the one to
¤ which the message is referring to. The msg say thta the OleDb provider
¤ is not registered on the current machine. But actually it is. In fact
¤ we can get connected by using msdaora.
¤

But those are two different OLEDB providers, each from a different vendor. The MSDAORA OleDb
provider is from Microsoft and is usually already installed. The OraOLEDB.Oracle OleDb provider is
from Oracle and is not installed unless someone explicitly installed it.

Try checking the bin subfolder in your Oracle client installation folder. There should be a file
called OraOLEDB.dll in addition to some other OraOLEDB files. If these files are not present then
this provider needs to be installed.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Paul Clement]

On 21 Jun 2006 11:35:37 -0700, (e-mail address removed) wrote:

¤ Hi Paul.
¤
¤ The Oracle client is installed because it has been installed on purpose
¤ for the connection and I have seen it being installed by the
¤ administrator.
¤
¤ Note that the msdaora does not behave like if it were a full client,
¤ infact it does *NOT* work at all if the Oracle Client is not installed.
¤ It just appears to be as a layer of software which talks with the
¤ Oracle client (which is installed).
¤
¤ I dont' really get what the connection string containing msdaora does
¤ different with respect to the other one.

Maybe I wasn't clear. They are different drivers. MSDAORA is from Microsoft and the other one, which
isn't working for you, is from Oracle. When you use a DSN-less connection string you specify which
provider (or driver) to use.

I would recommend installing (or reinstalling) the Oracle provider (not the client software) because
it doesn't appear to be installed (or installed properly).


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[tommaso.gastaldi]

Hi Paul,

Ok I think I see now what you mean. You are sayng that the installation
of the Oracle Client does not install by default the OleDb provider. So
when the Administrator installed the client probably he did not select
to install the OleDb drivers too. Is that what you mean?

In such a case I guess that one should reinstall the Oracle client
making sure that also the OleDb providers is selected for installation.
Right?

Thank you very much for your help.

-Tom

Paul Clement ha scritto:

 

[Paul Clement]

On 22 Jun 2006 13:14:39 -0700, (e-mail address removed) wrote:

¤ Hi Paul,
¤
¤ Ok I think I see now what you mean. You are sayng that the installation
¤ of the Oracle Client does not install by default the OleDb provider. So
¤ when the Administrator installed the client probably he did not select
¤ to install the OleDb drivers too. Is that what you mean?

Exactly. They are separate installs.

¤ In such a case I guess that one should reinstall the Oracle client
¤ making sure that also the OleDb providers is selected for installation.
¤ Right?
¤

If I remember correctly the client software and the OLEDB provider are separate installs on the CD.
I would *not* reinstall the Oracle client software if everything else is working. Only install the
OLEDB provider.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[tommaso.gastaldi]

Thanks Paul. Actually, all my perplexities derived by the (wrong)
assumption that the installation of the client would also install the
OleDb drivers. Your explanations have clarified everything.

By the way, if I may, according to your experience, are you able to
recommend one of these 2 drivers in particular, or they can be
considered just equivalent?

Thank you very much.

-Tommaso

Paul Clement ha scritto:

 

[Paul Clement]

On 23 Jun 2006 06:51:05 -0700, (e-mail address removed) wrote:

¤ Thanks Paul. Actually, all my perplexities derived by the (wrong)
¤ assumption that the installation of the client would also install the
¤ OleDb drivers. Your explanations have clarified everything.
¤
¤ By the way, if I may, according to your experience, are you able to
¤ recommend one of these 2 drivers in particular, or they can be
¤ considered just equivalent?
¤

The Microsoft provider (MSDAORA) was initially the more stable of the two drivers. However, it is no
longer updated to be current with newer versions of Oracle.

INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/kb/q244661/

The Oracle provider is now stable and Oracle has continued to update it with respect to newer
versions of the Oracle database product. For that reason I would recommend using Oracle's OLEDB
provider.

However, unless there is a reason you need to use OLEDB, and since you're using .NET, I would tend
to recommend using either Microsoft's .NET library for Oracle or Oracle's ODP.NET library instead of
either ODBC or OLEDB. The only caveat with respect to the ODP.NET library is that the minimum Oracle
client version required is 9i.

Hope I didn't make the choices any more confusing. ;-)


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[tommaso.gastaldi]

Thank you Paul.

Yes actually I have also always wondering if it were better to use, in
VB.NET,
OracleConnection or OleDbConnection.

One, in absence of information, would assume that the first one is
specialized to connect to Oracle and would expect it to be somehow more
efficient.

Actually I have instead always used the OleDbConnection (and related
classes OleDbDataReader, etc) because, in my program, I am allowing to
connect to different DBMS and therefore it is convenient to have a
unique connection mechanism. Also because this allows (and this is most
important for my purposes) to retrieve homogeneous information from the
OleDbSchemaGuid. As to performances, this seemed quite efficient,
although I did not measure the difference with the "specialized"
connectors.

Of course one could write some SELECT to distinguish Oracle / SqlServer
/ Others, but this would require to write several times the same code
and would make maintenance a nightmare.

Actually, it would be really nice if one could write unique interface
with appropriate properties as to allow to use with the same code all
the connection mechanisms. But I am missing to see a way to do it.

-Tommaso









Paul Clement ha scritto: