2 Application Development

2.1 Developing PL/SQL Web Applications

SQL developers familiar with the database can develop Web applications that exclusively use Oracle Application Server 10g and Oracle Database using the PL/SQL development environment. PL/SQL is a completely portable, high-performance transaction processing language that combines the data manipulation power of SQL with the data processing power of procedural languages.

The PL/SQL development environment lets you achieve the best performance for database-intensive applications because PL/SQL is highly optimized for use with the database through its support for and tight integration with SQL, support for processing an entire block of SQL statements at one time, and letting you compile PL/SQL procedures and store them in executable form in the database, to be called later. In addition, as a development environment, SQL developers have support for the object-oriented programming model, can experience higher productivity due to its procedural nature, and can come quickly up to speed to develop PL/SQL-based Web applications.

Developing Web applications using PL/SQL consists of developing one or more PL/SQL packages consisting of sets of stored procedures that interact with Web browsers through HTTP. Stored procedures can be executed in several ways:

• From a hypertext link that calls a stored procedure when it is selected.

• By clicking Submit on an HTML form to denote the completion of a task such as filling out a form supplied on the HTML page.

• By passing parameters to a stored procedure based on user choices from a list.

Information in the stored procedure, such as tagged HTML text, is displayed in the Web browser as a Web page. These dynamic Web pages are generated by the database and are based on the database contents and the input parameters passed in to the stored procedure. Using PL/SQL stored procedures is especially efficient and powerful for generating dynamic Web page content.

There are two ways of generating HTML output from PL/SQL:

• Using function calls to generate each HTML tag for output using the PL/SQL Web Toolkit package that is part of Oracle Application Server 10g and Oracle Database and whose owa packages are loaded into a common schema so that all users can access it.

• Embedding PL/SQL code in Web pages (PL/SQL server pages)

Use interMedia when media data such as images, audio, video, or combinations of all three are to be uploaded into and retrieved from database tables using the interMedia object types and their respective sets of methods.

Media upload procedures first perform a SQL INSERT operation to insert a row of data in the media table, which also initializes instances of the respective interMedia object columns with an empty BLOB. Next, a SQL SELECT FOR UPDATE operation selects the object columns for update. Finally a SQL UPDATE operation updates the media objects in their respective columns. interMedia methods are called to do the following:

• Initialize the object columns with an empty BLOB.

• Set attributes to indicate media data is stored internally in a BLOB.

• Get values of the object attributes and store them in the object attributes.

• When exceptions occur, determine the length of the BLOB content and its MIME type.

Media retrieval operations involve the following tasks:

• Retrieving the object from the database into a local object.

• Checking the cache validity of the object based on its updated time versus that of the HTTP header time.

• Determining where the media object is located: in the database, in a BFILE, or at a URL location; then, getting the media, and downloading it for display on an HTML page.

interMedia methods are called to get the time that the media object was last updated, to determine if the media is stored locally in the database, in a BFILE, or at a URL location, to get the MIME type of the media object, and finally to retrieve the media data.

Oracle Application Server 10g and Oracle Database install Oracle HTTP Server powered by the Apache HTTPD server that contains the PL/SQL Gateway to communicate directly with a client Web browser.

Oracle HTTP Server serves mainly the static HTML files, images, and so forth, that a Web application uses, and is usually located in the file system where Oracle HTTP Server is installed. Oracle HTTP Server contains modules or plug-ins that extend its functions. One of these modules supplied by Oracle is the mod_plsql module, also known as the PL/SQL Gateway. The PL/SQL Gateway serves data dynamically from the database to Web browsers by calling PL/SQL stored procedures. The PL/SQL Gateway receives requests from a Web browser in the form of PL/SQL servlets or PL/SQL server pages that are mapped to PL/SQL stored procedure calls. PL/SQL stored procedures retrieve data from the database and generate an HTTP response containing the data and code from the PL/SQL Web Toolkit to display the generated Web page in a Web browser. The PL/SQL Web Toolkit contains a set of packages called htp, htf, and owa packages that can be used in the stored procedures to get information about the request, construct HTML tags, and return header information to the client Web browser.

Figure 2-1 shows these main components of the PL/SQL development environment, Oracle HTTP Server (a component of Oracle Application Server 10g and Oracle Database), the Web browser, and the database. The following information describes how a client Web browser request is turned into a Web page response from the execution of the PL/SQL procedure:

1. A client Web browser sends a PL/SQL server page or servlet request to Oracle HTTP Server.

2. Oracle HTTP Server routes the request to the PL/SQL Gateway (mod_plsql).

3. The PL/SQL Gateway forwards the request to the database using configuration information stored in the database access descriptor (DAD) and connects to the database.

4. The PL/SQL Gateway prepares the call parameters and invokes the PL/SQL package and the PL/SQL stored procedure in the application.

5. The PL/SQL procedure generates an HTML page using data from the database and special packages in the PL/SQL Web Toolkit accessed from the database. The PL/SQL Web Toolkit contains a set of packages called htp, htf, and owa packages that are used in the stored procedures to get information about the request, construct HTML tags, and return header information back to the client Web browser as the response returned to the PL/SQL Gateway.

6. The PL/SQL Gateway sends the response to Oracle HTTP Server.

7. Oracle HTTP Server sends the response to the client Web browser for display as a formatted Web page.

Figure 2-1 Components of the PL/SQL Development Environment

Description of the illustration modplsql.gif

Usually, the returned formatted Web page has one or more additional links, and each link, when selected, sends another request to the database through the PL/SQL Gateway to execute one or more stored procedures. The generated response displays data on the client Web page usually with additional links, which, when selected, execute more stored procedures that return the generated response for display as yet another formatted Web page, and so forth. This is how the PL/SQL application in the PL/SQL development environment is designed to work.

Web application developers who use the PL/SQL development environment, create a PL/SQL package specification and body that describe procedures and functions that comprise the application. The package specification defines the procedures and functions used by the application, and the package body is the implementation of each procedure and function. All packages are compiled and stored in the database to perform specific operations for accessing data in the database and formatting HTML output for Web page presentation. To invoke these stored PL/SQL procedures, Web application developers use the request/response PL/SQL servlets and PL/SQL server pages (PSP) to allow Web browser clients to send requests and get back responses using HTTP.

Oracle HTTP Server maps a URL entered in a browser to a specific PL/SQL procedure stored in the database. It does this by storing specific configuration information by means of a DAD for each stored procedure. Thus, each DAD contains the database connection information that is needed by the Web server to translate the URL entered into a database connection in order to call the stored procedure.

Oracle HTTP Server listens for a request, routes the request to the PL/SQL Gateway, which forwards it to the database. Configuration information values stored in a DAD determine the database alias to use, the connection string to use for remote access, the procedure to use for uploading or downloading documents, and the user name and password information to allow access to the database. From the Web browser, the user specifies the URL that invokes the PL/SQL Gateway. The URL has a defined format specifying all required and optional parameters needed including the location of the DAD and the name of the PL/SQL stored procedure to run, as shown in Example 2-1.

protocol://hostname[:port number]/DAD-name/[[!][schema name.][package name.]procedure_name[?query_string]]

For a detailed description of each parameter and options available, see Oracle HTTP Server mod_plsql User's Guide. However, for the purpose of using the photo album application for interMedia and the PL/SQL Web Toolkit described in Section 3.1.1, the URL can be simplified to the format shown in Example 2-2.

protocol://<hostname>[:<port-number>]/DAD-name/]procedure_name

When the URL is entered in the Web browser, it includes the protocol (HTTP or HTTPS), the name of the hosting Web server, and the port number to which it is listening to handle requests. Next, the specified virtual path includes /pls/<DAD-name> to indicate that the Web server is configured to invoke mod_plsql, and the location of the DAD on the Web server.

In Example 2-1, the last five parameters include the exclamation point (!) character, schema name, package name, procedure name, and query string. From the syntax, the exclamation point, schema name, package name, and query string parameters are optional; only the procedure name is required.

The exclamation point indicates that flexible parameter passing is being used. The schema name, if omitted, is resolved based on the user name. The package name, if omitted, means the procedure is standalone. The query string parameters are for the stored procedure and follow a special format. Of these five parameters, the procedure name must be specified in both the DAD and the URL. The other four parameters are specified in either the DAD or the URL, or not at all, depending on the application.

The URL displays the home page for the specified DAD. When the URL is entered in the address field of the Web browser page, it invokes either the specified DAD location only, or the specified DAD location along with the procedure name, or the specified DAD location along with the schema.package.procedure name. The response is returned as an HTML page. The HTML page contains the requested data and any other specified code for display in the client's Web browser. The Code Wizard described in Section 3.2.1 illustrates how this works. For example, to invoke the Code Wizard administration URL, enter the following URL shown in Step 3 in Section 3.2.2:

http://<hostname>:<port-number>/pls/ordcwadmin

The virtual path includes pls to indicate that the Web server is configured to invoke mod_plsql, followed by the name of the DAD used for the Code Wizard administrator, ordcwadmin.

When the HTML page is displayed, it resolves to the following URL for the Code Wizard administrator:

http://<hostname>:<port-number>/pls/ordcwadmin/ORDCWPKG.menu

ORDCWPKG.menu represents the package.procedure name, which is specified as the default home page in the ordcwadmin DAD.

When the PL/SQL Gateway is invoked, it uses the stateless model and does not allow a transaction to span across multiple HTTP requests. In this stateless model, applications typically can create a session to maintain state by using one of the following techniques: HTTP cookies, a hidden HTML field as an HTML form element of the HTML Form package, or storage of vital information in database tables for query. For more information, see Oracle Database Application Developer's Guide - Fundamentals.

2.2 Developing Java-Based Web Applications Using JDBC

Java database connectivity (JDBC) is a standard Java interface defined by Sun Microsystems, based on the X/Open SQL Call Level Interface that complies with the SQL 92 Entry Level standard, that is used for connecting from Java to relational databases. JDBC supports dynamic SQL, letting a calling program construct SQL statements dynamically at runtime. These are the major benefits of using this Java interface in addition to allowing individual providers, such as Oracle Corporation, to implement and extend their own JDBC drivers.

A database-embedded JVM supports the JDBC interface. Java source code (.java file) is compiled into one or more byte code files (.class files) and these class files are interpreted at runtime and executed by the embedded JVM. If your Java application uses objects defined in other packages, you must set the CLASSPATH environment variable and specify the paths to all objects used by your application.

Resulting class files for servlets are usually placed in the directory enabled to run servlets. Class files for JSP are usually placed in the JavaBean directory for your servlet's container, while the JSP files are usually copied to a directory enabled to serve JSP, which by default, is your JSP sample application directory. For servlets, the actual location of your servlet class files depends on the servlet container you are using and how it is configured. For JSP, the actual location of your servlet and JSP files depends on the servlet container and JSP engine you are using, and how the engine is configured. Each of the Java sample applications uses the default location of the servlet containers as required by Oracle HTTP Server powered by Apache for an installation of Oracle Application Server 10g or Oracle Database. See the respective Java sample application readme.txt files for more information.

To write a Java application that uses the JDBC interface and the embedded JVM, perform the following operations that will access interMedia objects in a database table:

1. Establish a JDBC connection from the Java application to the database.

   Call a getConnection( ) method to obtain an OracleConnection object.

2. If your application will modify the interMedia object, perform the following operations:

   1. Call the setAutoCommit( ) method to disable auto-commit mode.

   2. Execute a SELECT... FOR UPDATE statement on the database table.

   Create an OracleStatement or OraclePreparedStatement object in your application. Call the executeQuery( ) method to execute the SELECT... FOR UPDATE statement and return an OracleResultSet object, and fetch a row from the result set.

3. If your application will not modify the interMedia object, execute a SELECT statement on the database table.

4. Retrieve the interMedia object from the result set as an instance of one of the classes of Oracle interMedia Java Classes.

5. Perform operations on the Java application object.

   Having retrieved the interMedia Java object from the result set, your application can now load new data into the object, or your application can retrieve or manipulate existing data in the object.

6. If the interMedia object has been modified by the application, update the database object to include the results of the operations, and commit your changes.

   If the application modified the object in the previous step, create an OraclePreparedStatement object that contains a SQL statement that updates the database object, and execute the statement.

   Commit the transaction by calling the commit( ) method.

7. Close the connection to the database table.

   The IMExample Java sample application, which is described in Chapter 4, provides examples of each step in this process.

   For more information on using JDBC, see Oracle Database JDBC Developer's Guide and Reference.