The OLE DB Provider for Oracle supports  most of the Oracle8 data types:

This table shows that many of the standard data types are supported but those such as the LOB (Large Object) and object-based extensions are not supported.

The provider is a native provider, in that it accesses the Oracle's API directly rather than through ODBC. This provides us with generally the best performance when compared to other methods of connecting to Oracle, but does mean that some functionality is not available.

In order to use the provider, you must set its name in the ConnectionString property of the ADO Connection object  or as the ConnectionString argument to the Open method. As with any provider for ADO, unpredictable results can occur if you specify the name of the provider in more than one place.

Let's start by connecting to the Oracle database using the scott username  to execute two simple built-in Oracle functions to retrieve the system date, sysdate, and current username, user:

Create a new ASP script called  GetDate.asp:

We use the Connection object's ConnectionString property to tell ADO how to connect to our Oracle database before calling the Open command to attempt to connect to the database. Don't forget that the Data Source  property, Oracle8_dev, is the service name that we created earlier, rather than the actual machine name – but in my case, both are actually the same value.

The Provider section tells ADO to use the OLE DB Provider for Oracle. We can use either
the class name of the provider, in this case MSDAORA, or the full provider name: 'OLE DB
Provider for Oracle'. As we want to use the scott account, we need to set the User ID
and Password accordingly.

Our Oracle8 server is located on a server called Oracle8_dev. You'll have to change this to reflect your own Oracle database server.

If you've not used the With...End With statement, it serves as a way to call multiple methods on a single object without having to refer to the name explicitly every time. It makes your code easier to read and actually runs slightly faster as the ASP processor doesn't have to do extra processing to establish the address of the objConnection object.

By calling the Open method, we should get a connection to the Oracle database. By way of a confirmation, we write out the name of the Provider property. This shows us the name as defined in the Registry along with any version number if there are multiple versions installed on the server.

The Execute method returns back a Recordset representing  the records that were fetched from the database, in this case a single record with a column containing the current system date and the current user name. The argument passed to Execute is the command that we want Oracle to run for us.

Notice the word dual in our SELECT statemen t? Oracle does not allow you to execute a SELECT statement without an accompanying FROM clause; dual is a logical pseudo-table, available to all accounts, provided for that purpose. It is not a physical table that you can alter.

We finish off by reading the Fields collection of our objRecordset  object to get the value for the sysdate and user functions. In the case of the user field we've left out the .Fields statement as this is the Recordset object's default property, though you can make your code run faster if you use it.

There's no need to navigate through the objRecordset, as there will only be one record returned. With any objects that we create in our scripts, it's always a good idea to shut them down explicitly as soon as possible using the Set ... = Nothing statement in order to free up server resources.

That was a relatively easy example, so let's have a look at a more complex statement in which we return a number of records. The scott schema comes with four sample tables that you can look at yourself. The tables opposite represent an employee's bonus and salary tracking  system:

This isn't the best schema that Oracle could have used as their pre-configured sample database. The SALGRADE and BONUS tables are not referenced by any other tables and contain no primary keys.

Our example ASP script will be using the DEPT and EMP tables to show a list of all employees sorted by their name  (later on we'll be using them in our sample application):

The code used to produce the previous screenshot looks like this:

We start off as before by defining two variables for our Connection and Recordset objects and then connect to the database using the scott/tiger combination. We've added a new variable, varSQL, to hold a nicely formatted SQL statement:

The SQL statement joins the employee table, EMP, to the department, DEPT, to return a list of employees and their departments. Again we use the Execute command to return back a Recordset
of data:

We use Response.Write to write out the start of our table of results:

Now it's just a case of writing out each record by retrieving the value for each column from the Fields collection of our Recordset object objRecordset  and moving to the next record using the MoveNext method. We loop through using a Do While...Loop that will stop as soon as it gets to the end of the Recordset.

Some of the records in the comm column contain a null value, so we add the HTML non-breaking space tag ( ) to ensure that the browser draws the cell border correctly.

As with our previous example, it's a good idea to explicitly close our objConnection and objRecordset objects as soon as we've finished with them.

We've now managed to connect to an Oracle8 database using the OLE DB Provider for Oracle to retrieve a single record of the current system date and username and a full list of employees in the scott database's emp table. It is suggested that the OLE DB Provider for Oracle be used for the majority of Oracle data access as it executes faster and supports Microsoft's new direction in data access – OLE DB.

Microsoft ODBC Driver for Oracle

The Microsoft ODBC Driver for Oracle supports the same set of Oracle8 data types as the OLE DB Provider for Oracle. When using this driver with ADO, we are actually using the OLE DB Provider for ODBC Drivers (MSDASQL), which in turn uses the Microsoft ODBC for Oracle Driver.

Microsoft released the OLE DB Provider for ODBC (MSDASQL) so that all existing ODBC-based applications could use the new features found in OLE DB through ADO. When connecting to any data source using ADO, this is the default provider that is used .

ODBC connection strings use the older DRIVER=, DSN=, UID=, PWD= and SERVER= (optionally in the place of DSN=) parameters to connect to a data source. Don't forget that there must be a valid Data Source Name, DSN, registered through the ODBC Data Source Administrator in the Administration Tools (or Control Panel) if you are going to use the DSN parameter .

Each time you connect to a database using a DSN, ODBC must look through the Windows Registry in order to retrieve connection details for your DSN. There may be some performance improvements in your application if you use DSN-less connections, as the Windows Registry is notoriously slow to access. If you do need to use DSNs, then remember to use System DSNs rather than File DSNs as anonymous users, which your server is more than likely to use, have access to them.

We are going to create a simple ASP script that uses some of the principles we used with the OLE DB Provider for Oracle to show a list of departments from the scott database's dept table.

The only real difference to this code is the connection string used, so we'll just show that line of code:

We make use of the DRIVER property to tell MSDASQL to use the Microsoft ODBC Driver for Oracle, SERVER points to our database server, Oracle8_dev, and we use UID and PWD rather than the User ID and Password combination .

Notice that we didn't specify a DSN so we don't have to create one, and although it's not actually necessary in this case, we've specified the name of the Provider to use.