[Download Sample]

Requirements

1. Your favorite IDE or text editor (my screenshots are of Visual Studio .NET 2003).
2. Oracle Database.
3. ODP.NET Data Provider (Documentation can be found here).
4. General knowledge of stored procedures and SQL.

In this example, I will show how to create a Web form that will allow you to insert three authors’ email addresses into an Oracle database by means of a stored procedure, all in a single round trip.

Establish your Database Schema

The first step is to set up our table and stored procedure.  I have included two PL/SQL scripts to aid you here. The first script will generate the table and stored procedure; the second script will drop the table and stored procedure.  I believe firmly that when you compose a script that will alter a database, you should immediately compose a script that will restore the database to its original state.

Figure 1 – PL/SQL Create Script

-- Author:        Steven M. Swafford
-- Date Created:  22-FEB-2005

-- Create table
create table ASPAllianceAuthors
(
  AuthorEmail      varchar2(100),
  AuthorFullName   varchar2(150)
)
;

-- Add comments to the table 
comment on table ASPAllianceAuthors
  is 'Holds suthor information';
  
-- Add comments to the columns 
comment on column ASPAllianceAuthors.AuthorEmail
  is 'email address';
comment on column ASPAllianceAuthors.AuthorFullName
  is 'full name';

-- Create procedure
create or replace procedure Authors_Array(pEmail VARCHAR2, pName VARCHAR2) is
begin
  INSERT INTO aspallianceauthors (authoremail, authorfullname) VALUES (pEmail, pName);
  Commit;
end Authors_Array;
/

Figure 2 – PL/SQL Revert Script

-- Author:        Steven M. Swafford
-- Date Created:  22-FEB-2005
      
-- Drop table(s)
DROP TABLE ASPAllianceAuthors;

-- Drop procedure
DROP procedure Authors_Array;

To run these scripts you may use SQL*Plus or any other product such as Toad or PL/SQL Developer (of these, I favor the latter).

Figure 3 – SQL*Plus Interface

1. Enter the appropriate user name.
2. Enter the appropriate password.
3. Enter the appropriate host string.

Once you have successfully logged into your database, run the following:

@c:\yourscriptlocation\Article621_Apply_Table.sql

You will need to change the folder location to where you have extracted the sample code that accompanies this article. Also, if you’re not familiar with what the @ sign indicates in this instance, the answer is that a single @ is equivalent to the START command and is used to run a SQL*Plus command script.

Once again I prefer PL/SQL Developer, and all future screenshots of the database will be from this product. If your script ran successfully, you should now see something comparable to the following.

Figure 4 – ASPAllianceAuthors Table

Figure 5 – Authors_Array Stored Procedure