We are going to bring together all of the concepts discussed  so far into a small ASP application based around the scott database schema. This application will show a list of employees from the emp table and allow  the user to perform the usual data-entry procedures:

To implement this application we will be using four ASP script files, an include file, and the global.asa file . The include file is an ADO helper file that we have created ourselves called ADOFunctions_inc.asp used to create our database connections as needed.

It is often a good idea to rename your included ASP files from .inc to .asp to prevent unauthorized people from simply opening them in a browser. We've done this with ADOFunctions_inc.asp as it contains a username and password which we don't want people to have access to. I've kept the _inc suffix so  that I know it's an include file.

We will be retrieving lists of data using simple SELECT statements whereas the add, edit, update and delete functionality will be provided by four Oracle stored procedures. This will let us examine how we go about calling Oracle stored procedure using INPUT and OUTPUT parameters with the aid of the Microsoft OLE DB Provider for Oracle.

It is notoriously difficult to retrieve an ADO Recordset from an Oracle stored procedure. Oracle simply does not allow us to execute a SELECT statement from within stored procedure without assigning the returned values to a PL/SQL variable using the INTO keyword. There is a way to achieve this functionality with ADO using PL/SQL tables or by using reference cursors. In the next section, we will be covering the retrieval of an ADO Recordset from Oracle stored procedures using PL/SQL tables and then we'll look at doing the same thing using reference cursors and a PL/SQL package.

One word of warning though, in order to concentrate on the Oracle fundamentals, we won't be using any DHTML features, so the screens do look rather bland!

global.asa

We won't use global.asa to  handle application and session events, but we will use it to add a reference to the ADO type library to all of our ASP scripts. This will allow us to use the constants such as adCmdText for our ADO Command object. Enter the following line into global.asa:

This uses the METADATA tag to include a TYPELIB file from the location specified. This is the default location into which the ADO library is located, but you should update it to reflect your own
installation if it is different. By adding this line we can make use of all of the standard ADO constants and enumerators.

Traditionally, ASP developers would include the Microsoft ADO include file, ADOVBS.inc, in order to refer to the ADO constants. This would have to be done on every ASP script and is potentially difficult to support. By using the METADATA tag you only have to declare it once which is faster for your web server .

ADOFunctions_inc.asp

This include file is used in all of our ASP scripts  that need to connect to the database. It is much better to put commonly used code into a single include file and reference that in each of our pages, as there would be only one place in which we need to change the username and password if we ever needed to.

So create a new folder called includes and add a new file called ADOFunctions_inc.asp containing the following code :

The GetDBConnection function simply returns an ADODB.Connection object which points to our Oracle database using the scott account.

Default.asp

Our home page, Default.asp, displays a list of all employees from the emp table using a SELECT statement ordered by name. This  page allows the user to create a new employee record by clicking the create employee link, delete an employee by pressing the Delete link, or edit an employee by clicking the employee's name. Both the edit and add employee link go to the EditEmp.asp page.

So let's have a look at the ASP code behind this page:

As usual we start off with the Option Explicit statement so that we must declare all variables and constants used in our code. We don't want this page to be cached by the browser so that any amended records are displayed each time the page is shown. We achieve this using Response.Expires = 0 to tell the browser that this page expires immediately. If your site is going to be accessed by users in different time zones then it's a good idea to actually set this to a large negative number.

The line <META> tag  is used to tell any proxy servers that they should not cache this page for the
same reason.

You'll notice this is the first time that we include our ADOFunctions.inc using the
#include directive .

The objRecordset variable stores the result of our SELECT statement executed by calling the GetDBConnection function to return an ADO Connection.

As with SQL Server, Oracle also supports table nam e aliases that can be used for long or duplicated tables, such as:

SELECT EmpHol.Name
FROM EmployeesOnHoliday EmpHol
WHERE EmpHol.Department=1

Now we fill out the table with the data:

We navigate through the records contained in the Recordset object, creating a table row for each employee. We cache the employee number as it is used as part of the URL for the hyperlink to EditEmp.asp.

We finish by closing off the ASP script and defining the local JavaScript function deleteEmployee. This function uses the window.confirm function to confirm whether the record should be deleted. If Yes, then the employee delete script, DeleteEmp.asp is called.