Connecting to an Oracle database
There are a number of ways in which
we can connect to an Oracle database in order to manipulate its data from within
our ASP scripts. Which one you use rather depends what you are trying to achieve
and whether your organization prefers access through stored procedures, as the
features supported by one method may not be supported in another.
As well as Oracle Corporation, there are
many third-party vendors such as Microsoft and Intersolv that provide a number
of products to communicate with Oracle. The following list represents the more
commonly used tools :
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for ODBC
Microsoft ODBC Driver for Oracle
Oracle ODBC Driver
Intersolv's Merant range of OLE DB providers
and ODBC drivers
Oracle Objects for OLE by Oracle
Oracle Provider for OLE DB by Oracle
Microsoft's Universal Data Access (UDA)
initiative contains a set of tools that we can use
to communicate with an Oracle database. With the integrated Microsoft Data Access
Components (MDAC) suite we can use ActiveX
Data Objects (ADO) in conjunction with the Microsoft
OLE DB Provider For Oracle (MSDAORA.DLL)
or the Microsoft
ODBC Driver for Oracle (MSORCL32.DLL)
to communicate effectively with Oracle in a way that is reliable, scalable and
offers high performance when using ADO.
Microsoft also offers the universal OLE
DB Provider for ODBC Drivers (MSDASQL.DLL)
that allows any ODBC data source to make use of the improvements in OLE DB.
This, the default provider used by ADO, was developed so that any existing ODBC-based
data could fit into the UDA environment efficiently and without losing an organization's
ODBC investment .
As if this didn't give us enough flexibility,
we also have the universal Merant
range of OLE DB providers and ODBC drivers from Intersolv
(www.merant.com/products/datadirect/oledb
/Connect/factsheet.asp), and Oracle
Objects for OLE (OO4O).
We've discussed how Oracle8's Net8 networking
component is used to communicate with an Oracle8 database, but we haven't mentioned
the Oracle
Call Interface (OCI) library .
We won't go into much detail except to say that this low-level layer exposes
certain procedures that the OLE DB providers and ODBC drivers call in order
to communicate with the database, in much the same way as DBLib for SQL Server
databases.
After that brief overview, it is now time
to show you how to connect to an Oracle8 database using the more popular technologies
so that you can see the relative pros and cons of each in terms of feature support,
performance, and ease.
There are bound to be times
when you need the ability to fetch recordsets from an Oracle stored procedure
with ADO. At the time of writing you have no choice but to use the ODBC driver
for Oracle or Oracle's Oracle Provider for OLE DB, both of which will be covered
later.