Accessing Oracle 9i tables on a Linux box

[Ron Fluegge]

Will ADO.NET have any problems accessing Oracle 9i tables running on a Linux
box using the Microsoft .net managed provider for Oracle?

I am assuming not, but it won't be the first time that I have seriously
underestimated the issues involved in such a project.

I have looked at Oracle's .net managed provider and am not happy with their
implementation for our specific use.

Sorry for asking such dumb questions, but we are under a short timeframe to
respond to the client.

Thanks for any advice and comments...

Ron

RonFlueggeATswbellDOTnet

 

[Roy Fine]

Ron,

The Oracle .Net Managed client is built upon the Oracle Call Interface
(OCI) - and that is the same interface that the MSFT provider is built upon.
OCI is in turn implemented upon the Net8 protocol stack and the Two Task
Layer - which will effectively isolate you from any of the database server
implementation details. You should not be concerned with the platform used
by the server - it could be Solaris, HP, Linux, Windows NT/2000, etc - it
all works the same from the client side.

With respect to the ODP.NET implementation - I have found it quite stable.
Having programming in VC++ and OCI for the past 5 years, the .NET and C# are
a very welcome productivity boost, and performance with respect to OCI is
not noticably different on the things that we do - and with array binding,
and collection pararmeters with bulk binding for stored procedure, moving
large amounts of data across the network is VERY much improved over other
provider implementations.

regards
roy fine

 

[Ron Fluegge]

Roy,

Thanks very much for the clarification / confirmation.

Our app implementation design was to forego the specific benefits from using
Oracle's managed provider in favor of a compromise with the "wider"
implementation of a "data factory" or "data layer".

Our datalayer is implemented to use three managed providers:

System.Data.SqlClient
System.Data.OleDb
System.Data.OracleClient

for interfacing with SQL Server 7/2000, Microsoft Access, and Oracle 8i/9i
respectively. We have SQL scripts that can build the required tables in any
of the three databases.

There is a string in an XML file that points to the specific set of tables
in one of the three RDBMS -- that is all that is required to switch from one
to the other in the data factory/layer. The reason for this approach was to
attempt to maintain a single code base regardless of client implementation
of the database system. (This is a commercial application not customized
for specific client implementation and is not data intensive such as an
airline reservation system might be.)

The datalayer receives a SQL statement from the business layer and
"translates" it into the required format for the specific provider -- this
very important for parameterized queries.

As a result, we have found it more advantageous to use the Microsoft .net
managed provider for Oracle under our design and test conditions. We have
not had specific problems with Oracle's provider, but made a design decision
up front based on the best available info and very limited testing at the
time (time constraints in getting a product to market) -- and went on from
there ...

However, this is the first time the question has come up about the Oracle
RDBMS being on a Linux box so we had no reference point to work with.

Ron

Ron,

The Oracle .Net Managed client is built upon the Oracle Call Interface
(OCI) - and that is the same interface that the MSFT provider is built upon.
OCI is in turn implemented upon the Net8 protocol stack and the Two Task
Layer - which will effectively isolate you from any of the database server
implementation details. You should not be concerned with the platform used
by the server - it could be Solaris, HP, Linux, Windows NT/2000, etc - it
all works the same from the client side.

With respect to the ODP.NET implementation - I have found it quite stable.
Having programming in VC++ and OCI for the past 5 years, the .NET and C# are
a very welcome productivity boost, and performance with respect to OCI is
not noticably different on the things that we do - and with array binding,
and collection pararmeters with bulk binding for stored procedure, moving
large amounts of data across the network is VERY much improved over other
provider implementations.

regards
roy fine

< truncated >

 

[Roy Fine]

Ron

all points well taken....

MSFT and Oracle more or less universally abandoned the common interface, and
developing interfaces that take best advantage of the specific provider.
ADO/OLEDB as was the last universal interface, and even that had it
differences.

we are primarily an Oracle shop here, and have been for many years, but we
do have the opportunity to do work from time to time where the database is
pluggable - Oracle or SqlServer. Our biggest problem is porting the stored
procedures, as we tend to move as much of the data manipulation as close to
the server as possible, and minimize network traffic to strictly that which
needs to be visualized.

a separate application specific data access layer is a very common approach.
all the best.

regards
roy fine

 

[Ron Fluegge]

Roy,

Again many thanks for the info.

One of the reasons for not using stored procedures is the need to ensure
that our queries don't get altered by the client's dba. Generally, we find
that most dbas will leave the stored procedures alone, but every so often
the client's dba wants to fiddle with stored procedures so we have moved the
"parameterized" queries into the code for save keeping ... again, another
"compromise" to meet the design constraints of curious clients <grin>.

Ron

Ron

all points well taken....

MSFT and Oracle more or less universally abandoned the common interface, and
developing interfaces that take best advantage of the specific provider.
ADO/OLEDB as was the last universal interface, and even that had it
differences.

we are primarily an Oracle shop here, and have been for many years, but we
do have the opportunity to do work from time to time where the database is
pluggable - Oracle or SqlServer. Our biggest problem is porting the stored
procedures, as we tend to move as much of the data manipulation as close to
the server as possible, and minimize network traffic to strictly that which
needs to be visualized.

a separate application specific data access layer is a very common approach.
all the best.

regards
roy fine

< truncated >

 

[Thomas H]

Roy,

Again many thanks for the info.

One of the reasons for not using stored procedures is the need to ensure
that our queries don't get altered by the client's dba. Generally, we find
that most dbas will leave the stored procedures alone, but every so often
the client's dba wants to fiddle with stored procedures so we have moved the
"parameterized" queries into the code for save keeping ... again, another
"compromise" to meet the design constraints of curious clients <grin>.

Ron

Ron, not to jump in here, but why not use the wrap.exe function that Oracle
provides? It will encrypt your stored procedures. You can find it in your
oracle_home\bin directory, and Oracle has documented it very well- look it
up in the "PL/SQL User's Guide/Reference" manual. (For 10g, that'd be doc #
B10807-01)

Also, you can run Linux on a relatively old & cheap machine. I had to
install Red Hat Enterprise Linux 3.0 Advanced Server to do some Oracle
testing, and it was able to install and run on a P3/800 with 256 MB of
memory and a 12 GB hard drive. You might want to drag an old box out of the
closet and turn it into a "permanent" Linux test server.

-Thomas

 

[Ron Fluegge]

Thomas,

cool ... Thanks for the info...!!

We have created a data factory that uses the individual managed providers
for SQL Server 7/2000, Oracle 8i/9i and Microsoft Access and can switch to
any of the 3 by simply changing a string in an XML file.

We chose to try to develop a single codebase in the data factory that would
handle any and all 3 so we have had to make a number of "compromises" to
achieve our design objective. As a result, we have to steer away from
Microsoft or Oracle "specific" code or implementations. We can get away
with this because our app is not "user intensive" or "data intensive" like
an airline reservation system might be.

But it is always nice to learn something new so your advice and comments are
very, very much appreciated.

Again, many thanks ...

Ron