Three Approaches to Oracle Dynamic Content with the Dell ...
Three Approaches to Oracle® Dynamic Content with the Dell™ PowerEdge™ 1650
1 Enterprise Systems Group (ESG)
Dell White Paper
2 By Dave Jaffe
3 dave_jaffe@
4 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
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 ().
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. ), 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. ).
The three implementations are discussed in detail in the following sections. The output of all three dynamic pages is shown in Figure 1.
[pic]
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 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 () 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
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- solutions chapter 1 sql and data test your thinking
- oracle supercluster m7 series upgrade configuration
- three approaches to oracle dynamic content with the dell
- first steps towards oracle 10g
- oracle quick reference guide devereux
- answers to selected questions and problems
- introduction claros data management hach