Three Approaches to Oracle Dynamic Content with the Dell Poweredge 1650

Three Approaches to Oracle® Dynamic Content with the Dell™ PowerEdge™ 1650

Enterprise Systems Group (ESG)

Dell White Paper

By Dave Jaffe

June 2002

Contents

Executive Summary 3

Introduction 4

The Application 5

The Oracle Stored Procedure 5

The Dynamic Web Pages 5

Approach 1: Application Server Page 7

Approach 2: JavaServer Page 9

Approach 3: Oracle PL/SQL Page 11

Conclusions 12

Acknowledgements 13

Bibliography 14

Appendices 16

Appendix A top10_tab.get_top10 16

Appendix B top10_rec.get_top10 20

Appendix C top10.asp 23

Appendix D global.asa 26

Appendix E top10.jsp 27

Appendix F web.xml 29

Appendix G top10.psp 30

Appendix H Installing Tomcat 4.0.3 on Red Hat Linux 7.2 32

Figure 1 Output of top10.jsp, top10.asp and top10.psp 6

June 2002 Page 32 Dell Enterprise Systems Group

Section 1

Executive Summary

To demonstrate the flexibility of the Dell™ PowerEdge™ 1650 rack-dense server, three different methods of accessing Oracle data were implemented as dynamic web pages on the PowerEdge1650. The three approaches – Active Server Pages running under Microsoft’s Internet Information Server on Microsoft® Windows® 2000 Server, JavaServer Pages running under Tomcat/Apache on Red Hat® Linux® 7.2, or Oracle® PL/SQL Pages running in either environment – all run well on the PowerEdge 1650. The flexibility and cost-effectiveness of the PowerEdge 1650 make it well suited for any kind of application.

Section 2

Introduction

The Dell PowerEdge 1650 application server provides a rack-dense, cost-effective gateway to data stored on a backend Oracle database. Configured with one or two 1.4GHz Pentium III processors, dual integrated gigabit Ethernet network interface cards (standard) and up to 4 GB of memory, the 1.75-inches high (1 rack unit or 1U) PE1650 is well suited as the middle tier of a multi-tiered server stack. Available with either Microsoft Windows 2000 Server or Red Hat Linux 7.2, the PowerEdge1650 can host a multitude of mid-tier applications.

The three application interfaces demonstrated in this paper, from the Microsoft, Oracle, and open source/Java camps, represent three of the most popular methods of accessing dynamic content from Oracle databases. All three applications run similarly. Each consists of a single dynamic web page that opens a connection to the backend Oracle database, calls an Oracle stored procedure, and formats the returned data into a Hypertext Markup Language (HTML) page.

All three dynamic pages return user-selected Top 10 lists from a baseball database. In the first approach, top10.asp, a Microsoft Active Server Page running under Microsoft Internet Information Server (IIS) opens an Open Database Connectivity (ODBC) -based connection to the Oracle database. In the second approach, top10.jsp, a JavaServer Page running under Tomcat from the Jakarta project of the Apache Software Foundation, opens a Java Database Connectivity (JDBC) connection into the same Oracle database. The third approach is an all-Oracle solution. An Oracle Procedural Language/Structured Query Language (PL/SQL) page, top10.psp, running under Apache on Oracle 9i™ Application Server calls a slightly different stored procedure in the Oracle database.

Demonstrating the flexibility of the PowerEdge line of servers, all of these applications are operating system neutral. The JavaServer Page implementation in this study was on Red Hat Linux, but Tomcat runs equally well under Windows. Oracle 9i Application Server runs well under either Windows or Linux. Even Active Server Pages can run under Linux/Apache using Chili!Soft!™.ASP (http://www.chilisoft.com).

The Oracle stored procedure and the overall application are discussed in Section 3, The Application. Details of the three implementations follow in the next three sections. All source code is included in Appendices.

Section 3

The Application

The Oracle Stored Procedure

The Major League Baseball Top 10 queries are an outgrowth of the work done in Paper 6 in Section 8, Bibliography. Oracle setup and the baseball database organization are described in depth in that publication.

For this study, a new PL/SQL stored procedure, get_top10, was defined. In the first version, in the package top10_tab (see Appendix A, top10_tab.get_top10) a cursor variable is used with 20 different select statements to return the top 10 leaders in any of 20 different statistical categories (home runs, hits, etc.), in any year from 1901 - 2000, and in either the American League, the National league, or both. In this version, the result data (the ten leaders’ first name, last name, team city, team nickname, and the value of the statistic) are returned as five separate Oracle PL/SQL index-by tables (similar to arrays). A slightly different second version of the procedure, in the package top10_rec (see Appendix B, top10_rec.get_top10), is used by the PL/SQL version of the dynamic page and passes the data back as an array of Oracle PL/SQL records, taking advantage of the fact that both the dynamic page and the stored procedure are written in PL/SQL.

In both versions of the stored procedure there are four inputs: input_stat_type, input_year, input_league, and batch_size (usually set to 10), and one scalar output variable, found, indicating the number of rows found for the query (which is always 10 with this data).

To access Oracle on the backend database server, the Oracle9i Database client code must be installed on the PowerEdge1650 hosting the applications. This provides the ODBC or JDBC connector for ASP and JSP pages, respectively. The PL/SQL page requires that Oracle9i Application Server be installed on the PowerEdge 1650. This provides the PL/SQL Gateway using the mod_plsql module of the Oracle HTTP Server Powered by Apache.

The Dynamic Web Pages

The three dynamic pages created for this study all operate similarly. By imbedding code (VBScript, Java or PL/SQL) into HTML, each page fulfills the dual roles of presenting a form to the user and calling the Oracle stored procedure, depending on how it was called. If called without a query string (e.g. http://dell8.ascisp.com/baseball/top10.jsp), a fill-in form with three drop-down menus is created to enable the user to select the statistic, year and league of interest. Upon the user clicking on the Submit Query button, the form calls the same web page, this time as a GET command with an attached query string (e.g. http://dell8.ascisp.com/baseball/top10.jsp?stat_type=ERA&year=1978&league=AL).

The three implementations are discussed in detail in the following sections. The output of all three dynamic pages is shown in Figure 1.

Figure 1: Output of top10.jsp, top10.asp and top10.psp

Section 4

Approach 1: Application Server Page

The Microsoft Application Server Page (ASP) approach utilizes the ActiveX Data Object (ADO) ODBC component of ASP to open a connection to Oracle and accept the returned data as the rows of an ADO Recordset. The top10.asp page, along with a second short program, global.asa, are placed in a directory which is made a virtual directory, /baseball, under IIS, using the Internet Services Manager.

In the top10.asp code (see Appendix C), first the <%@ Language=VBScript %> header is used to define the code interpreter. This designates that any code included between <% … %> pairs will be interpreted as VBScript. Then the ASP Request object is used to retrieve values for the three input parameters: stat_type, year, and league. If this is the first time the page was called these three will be initialized with an empty string. If these parameters have been set an IF-THEN-ELSE IF –ELSE statement is then used to initialize the value of the parameter league_fullname based on the value of league (AL, NL, or _L for both leagues).

At this point in the code, an HTML form is generated to enable the user to select the desired statistic, year and league. A one-line Javascript function is used to generate the years 1902-2000 in the “year” drop-down menu. The HTML title is modified depending on whether this is the first time the page has been accessed or if a query has already been submitted. The action of the form, when Submit Query is pressed, is to call the same page with a GET command, with the three input variables making up the query string.

Following the HTML form, if a query has been submitted (i.e., if the value of stat_type is not an empty string), the result table is initiated with the HTML code for the Table headers, then a large chunk of VBScript opens a connection to the backend Oracle database, calls the top10_tab.get_top10 stored procedure, and places the results into HTML Table definition tags.

First, the conn Connection object is created and used to open the connection to the Oracle database server, ora6450d, using the Microsoft ODBC for Oracle driver. The userid and password are retrieved from the ASP Application object, which is initialized in the global.asa ASP program (see Appendix D), called the first time the application is loaded.

Next, the get_top10 Command object is created and linked to the conn object. The text of the get_top10 object is set to call the stored procedure top10_tab.get_top10 with the five scalar parameters (represented by question marks) and expecting a result set of five 10-row index-by tables representing the first and last name of each player, the city and nickname of the player’s team, and the value of the player’s statistic:

call top10_tab.get_top10(?,?,?,?,?,{resultset 10, firstname_array,

lastname_array, city_array, nickname_array, stat_array})

Five lines of code now define the five scalar parameters to the stored procedure. The first four (input_stat_type, input_year, input_league, and batch_size) are input parameters and the fifth (found) is an output parameter. Following that a Recordset object, rsResult, is created and its Source is set to the get_top10 Command object. The input parameters are filled in with the stat_type, year and league data returned by the HTML form.

Finally, the rsResult.Open line calls the stored procedure and rs.Result is filled in with the elements of the five returned arrays. A While loop is used to generate HTML Table tags with this data. The Recordset and Connection are closed, and control passes back to the HTML page, which is completed and sent to the user.

Section 5

Approach 2: JavaServer Page

The open-source JavaServer Page and Servlet engine, Tomcat, from the Jakarta project of the Apache Software Foundation (http://jarkarta.apache.org) was used to provide a container for the JSP version of the application, top 10.jsp (see Appendix E). Red Hat Linux packages with Tomcat 4.0.3 and supporting services were downloaded from the Jakarta site and installed with the Red Hat Package Manager. A context for the baseball application is defined in the server.xml file, and a web.xml file containing the Oracle userid and password is created. Finally, Apache is configured to use the Warp connector to send requests for the /baseball virtual directory to Tomcat. Complete installation details are in Appendix H.

The functionality of top10.jsp is very similar to that of top10.asp, described in the previous section. At the top of the page, the

<%@ page language="java" import="java.sql.*, java.math.*,

oracle.jdbc.*, oracle.sql.*” %>

header specifies the Java language as well as tells the page which Java packages to import. As in the ASP page, the request object is then used to retrieve the stat_type, year and league input parameters from the page if the page is being called as a result of the HTML Form being submitted. If this is the first time the page has been called the variables will have the value “null”. This is used in the next piece of code to determine whether to set league_fullname or not.

The HTML Form is then created exactly as in the ASP page. Upon submission the same Oracle stored procedure, top10_tab.get_top10, is called and the resulting data populates the elements of an HTML Table with the Top 10 leaders in the specified statistic, year and league.

The Java statements to access the stored procedure parallel the ASP code. The Oracle Java Database Connectivity (JDBC) driver is first registered and then used to open a connection, conn, to the backend database, using a userid and password obtained from the web.xml file local to the baseball application (see Appendix F). An instance of the OracleCallableStatement class (from oracle.sql), get_top10, is created, associated with conn and defined with the call

begin top10_tab.get_top10(?, ?, ?, ?, ?, ?, ?, ?, ?, ?); end;

In this call the question mark is use to designate both input and output parameters.

The setString or setInt method is then used to bind the input parameters stat_type, year, league and the batch size (10) to the first four variables. The next line registers the first output variable, the number of rows found by the query (always 10), as an integer. The next five lines register the five output index-by tables containing the first name, last name, team city, team nickname and statistic value of the 10 leaders in that specified statistical category for the specified year and league. The OracleType VARCHAR or NUMBER is used to specify the type of date contained in the index-by table.

Finally the stored procedure is executed and the output data is placed into HTML Table tags. The first output parameter, rows_returned, is parsed into an integer, followed by five invocations of the getPlsqlIndexTable method to read the five index-by tables into String or BigDecimal arrays. Then the statement is closed and the HTML Table tags are written using a Java loop. Upon conclusion of the loop the page is completed and sent to the browser.

Section 6

Approach 3: Oracle PL/SQL Page

Dynamic web pages implemented in the Oracle Procedural Language/Structured Query Language are the easiest way to call PL/SQL stored procedures and can take advantage of advanced PL/SQL features. In the case of top10.psp (see Appendix G), the dynamic page can handle the PL/SQL record type so it calls a different version of get_top10, in the top10_rec package. This version returns all the data in a single index-by table, each row of which is an Oracle PL/SQL record containing the player’s first and last names, team city and nickname, and statistic value.

Like all Oracle9i Application Server content (such as the Oracle Portal content described in Paper 6 in Section 8, the Bibliography), Oracle stores PL/SQL pages in the backend database along with the data. The command to load the PSP is

loadpsp -replace -user baseball/(password)@ora6450d top10.psp

The Oracle PL/SQL Gateway (implemented by the Apache mod_plsql module) creates virtual directories under /pls/(user). This results in a URL such as http://dell8.ascisp.com/pls/baseball/top10 for the top10.psp page.

As with the Active Server Page and the JavaServer Page implementations of the Top 10 dynamic web page, top10.psp consists of HTML interspersed with code inside <% … %> delimiters, in this case, PL/SQL code. As before, the first line of the page, <%@ page language="PL/SQL" %>, specifies the language. The input parameters stat_type, year and league are read from the query string or default to NULL if this is the first time the page is loaded.