16-3. Accessing PL/SQL Web Procedures with HTTP
Problem
You are developing a Java web application that uses an Oracle Database. You have already created a PL/SQL web application that displays some particular data from your database that is generated from an input identifier. You want to use the PL/SQL web application to display that data by passing the necessary input from the Java web application.
Solution
Write your PL/SQL web program to accept parameter values within a URL. Pass the values from your Java web application to the PL/SQL application by embedding them within the URL that calls it. When the URL is clicked, then it will redirect control to the PL/SQL application, passing the parameters that are required to display the correct data. Suppose, for example, that you are writing a Java web application that generates a list of employees on a web page. Suppose further that you have already written PL/SQL web application that, given an employee_id, displays employee record details in a browser. You want to combine that functionality with your Java program so that when you click one of the employees in the list generated by the Java web program, it passes the selected employee's ID to the PL/SQL web program. In turn, the PL/SQL program will display the detail for that ticket. In the following example, the EMP_RPT package that was introduced in Recipe 14-4 is accessed via a Java Server Faces page.
Note JSF is the Java standard for creation of server-side user interfaces. To learn more about this technology, please see the online documentation at www.oracle.com/technetwork/java/javaee/javaserverfaces-139869.html.
<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:ui="http://java.sun.com/jsf/facelets"
xmlns:f="http://java.sun.com/jsf/core"
xmlns:h="http://java.sun.com/jsf/html">
<body>
<ui:composition template="layout/my_layout.xhtml">
<ui:define name="body">
<f:view id="employeeView">
<h:form id="employeeResults">
<center>
<br/>
<h:messages id="messages"
errorClass="error"
infoClass="info" />
<br/>
<span class="sub_head_sub">
Employee Listing
</span>
<br/>
<br/>
<h:dataTable id="employeeList"
rows="20"
value="#{employeeList}"
var="emp">
<f:facet name="header">
<h:column >
<h:outputText value="First Name"/>
</h:column>
<h:column >
<h:outputText value="Last Name"/>
</h:column>
<h:column >
<h:outputText value="Email"/>
</h:column>
</f:facet>
<h:column id="firstNameCol">
<h:outputText id="firstName" value="#{emp.firstName}"/>
</h:column>
<h:column id="lastNameCol">
<h:outputText id="lastName" value="#{emp.lastName}"/>
</h:column>
<h:column id="emailCol">
<h:outputLink value="http://my-oracle-application-
server:7778/DAD/emp_rpt.rpt"
target="_blank">
<f:param name="emp_id" value="#{emp.employeeId}"/>
<h:outputText id="email" value="#{emp.email}"/>
</h:outputLink>
</h:column>
</h:dataTable>
</center>
</h:form>
</f:view>
</ui:define>
</ui:composition>
</body>
</html>
The JSF tags in this example would generate a web page that looks similar to Figure 16-1. However, it is important to note that JSF contains template functionality, so the look and feel of the user interface can be changed significantly if a different template were applied.
Figure 16-1. Employee listing JSF web page
For the sake of brevity, the Java code will not be displayed, because it is not essential for this solution. However, if you want to learn more about writing Java web applications utilizing the Java Server Faces web framework, please see the online documentation available at www.oracle.com/technetwork/java/javaee/javaserverfaces-139869.html.
When you look at the JSF page output on your monitor, you'll see that the EMAIL column values are blue. This signifies that they are links that will take you to another page when selected with the mouse. In this case, the link will redirect users to a PL/SQL application that accepts the employee ID as input and in turn displays a result. Figure 16-2 shows the output from the PL/SQL web application when the e-mail user name SKING is selected from the JSF page.
Figure 16-2. PL/SQL web application output
How It Works
Developing Java web applications and PL/SQL web applications can be quite different. However, accessing one from the other can be quite easy and can create powerful solutions. In this recipe, a mashup consisting of a standard web URL passes data from a Java application to a PL/SQL stored procedure, and then the PL/SQL stored procedure displays content via a web page.
The PL/SQL stored procedure in this recipe utilizes the built-in UTL_HTTP package to display content in HTML format via the Web. The procedure accepts one argument, an EMPLOYEE_ID. The given EMPLOYEE_ID field is used to query the database, and the content that is retrieved is displayed. The procedure is accessible from the Web because a Data Access Descriptor (DAD) has been created on the web server, which allows access to a particular schema's web-accessible content. Using the DAD, a URL incorporating the host name, the DAD, and the procedure to be used can access the stored procedure. Please see Recipe 14-1 to learn more about creating DADs. For more details regarding the creation of web content using PL/SQL, please refer to Chapter 14.
The Java application Extensible Hypertext Markup Language (XHTML) page that is displayed in the solution to this recipe creates a listing of employee names by querying the database using EJB technology. Enterprise Java Beans (EJB) is part of the Java Enterprise Edition stack that is used for object relational mapping of Java code and database entities. For more information regarding EJB technology, please refer to the documentation at www.oracle.com/technetwork/java/index-jsp-140203.html.
The important code for this particular recipe is the web page code that resides within the Java Server Faces XHTML page. The generated list of employee names is a list of URLs that contain the host name of the Oracle Application Server, the DAD for the schema containing the PL/SQL you want to access, and the name of the PL/SQL stored procedure, which is EMP_RPT.RPT in this case. The URL also contains an embedded parameter that is passed to the stored procedure upon invocation. The following code shows an example of a URL that is generated by the Java application:
<a href="http://my-web-server:port/hr/EMP_RPT.RPT?emp_id=200">
The code that generates this URL is written in Java Server Faces using Facelets markup, as shown here:
<h:outputLink value="http://my-oracle-application-server:port/DAD/emp_rpt.rpt"
target="_blank">
<f:param name="emp_id" value="#{emp.employeeId}"/>
<h:outputText id="email" value="#{emp.email}"/>
</h:outputLink>
The &emp_id=200 portion of the URL is the parameter name and value that is passed to the EMP_RPT.RPT procedure when called. In the case of the JSF markup, #{emp.employeeId} will pass this value as a parameter to the URL. In turn, the EMP_RPT.RPT procedure queries the EMPLOYEES table for the given EMPLOYEE_ID and displays the record data. In a sense, the Java application performs a redirect to the PL/SQL stored procedure, as illustrated by Figure 16-3.
Figure 16-3. JSF to PL/SQL web redirect
Note Facelets is an open source web framework that is the default view handler technology for JSF.
Any two languages that can be used to develop web applications can be used to create mashups in a similar fashion. A regular HTML page can include links to any PL/SQL stored procedure that has been deployed and made available using a DAD. This is a simple technique that can be used to allow applications to use data that resides in a remote database.