Hi.

I was developing a web application using SQL Server. Suddenly, my
customer decided to use Oracle 8.1.6 as the database. The DBA is
deciding if the application will access a new database or a scheme and
I wiil have to migrate my SqlHelper class to an OracleHelper class.
What is the purpose of an Oracle scheme? Can I access it using the
..NET Oledb Provider?

Thanks,
Robert Scheer

"Robert Scheer" <rbscheer (AT) my-deja (DOT) com> wrote

An Oracle Schema is a logical collection of all of the database objects
owned by a security principal. The security principay may or may not be a
user account - the account may not support a connection to the database, but
may have granted access to the objects (tables, views, packaged, functions,
sequences, etc) to other security principals or schemas.

The DBA may be thinking of creating a new database for you on an new
machine, or a new instance of a database on an existing machine with Oracle
already installed, or may be thinking of creating a schema for your
application on an existing instance. Which of these the DBA does is
absolutely of no concern to you or the application. The DBA will create the
security principal (i.e. user account), then allocate that to that account a
default tablespace, a temp tablespace, and maby a few roles and permissions
so the account can support logon, object creation, DML and SQL Selects, and
so on.

Your first worry should be to decide on the .NET provider - Oracle from
MSFT, Oracle from Oracle, or OLEDB/ODBC bridge. If you had lots of SQL
Server stored procedures, then you need to get them rewritten. If you used
named notation for .Net bind variables, change to positional notation.
that's just a few thoughts - I sure many will provide more.

regards
roy fine

rbscheer (AT) my-deja (DOT) com (Robert Scheer) wrote in message news:<cfd22ab6.0406061015.20065562 (AT) posting (DOT) google.com>...

Thanks Roy and Gregory.

It seems that can't really use the Oledb Provider. In my researches I
found that Oledb does not support Oracle cursors. Can you confirm
that?
Also, I found that in order to use ODP.NET I need to install the
Oracle Client 9 but my database is 8i. Can I use this newer client to
access an older database without problems?

Thanks,
Robert Scheer

"Robert Scheer" <rbscheer (AT) my-deja (DOT) com> wrote

Robert -

Someone is giving you bad (or at least incomplete) info - OLEDB does in fact
support cursors - but it does not support updatable cursors -- all cursors
from Oracle are Read Only. There is the cursor that is opened with the For
Update clause, but that is still read only, and its advantage or a standard
cursor is that it includes the rowid as a hidden column, to be used in the
UPDATE..FOR CURRENT OF statement.

With respect to ODP.NET - yes you must have Oracle 9iR2 client installed.
You can still connect and access an Oracle 8i database, but obviously, you
will only be able to use 8i functionality in that case.

regards
roy fine

Hi Roy.

In fact, I made a test using the Oracle Oledb Provider that is
installed with the Oracle 9i Client and it worked to return ref
cursors. And the code is smaller that what I had to code using the
ODP.NET for the same task. But could not get the job done using
Microsoft's Oracle Oledb Provider.

After some tests and researches we decided to use ODP.NET as the
provider to access the Oracle database, mainly because of performance.
But I fear the poor documentation Oracle has available about ODP.NET.

Many thanks for your help.

Robert Scheer


"Roy Fine" <rlfine (AT) twt (DOT) obfuscate.net> wrote