Calling external Web Service from a Java Stored Procedure

Table Of Contents

• Overview of the Sample Application
• Application Architecture Diagram
• User Notes with Screen Shots
• Pre-Requisites
• Installation and Configuration
• Description of Sample files
• References

This sample illustrates calling a Web Service from within an Oracle9i Database. The scenario implemented in this demonstration is a simple purchasing module. A product catalog is displayed to the user. The user can browse through the catalog and purchase products of interest. Based on the login information supplied by the user, he or she is authenticated and the order is shipped on a later date.

This sample uses a Credit Agency Web Service which maintains a repository of customers and their credit card information. This web service exposes two methods - one to authorize the customer and the other to bill the amount of purchase to the customer's credit card account.

When the user initiates a purchase, the following sequence of events occur :

1. The user is expected to provide a customer id before placing an order.
2. This customer is authorized by the Credit Agency Web Service.
   
   • If the customer id is not valid, the web service returns 'INVALID_CUSTOMER'. In this case an appropriate message is displayed to the user.
   • However, if the customer id is a valid one then, the web service returns ' VALID_CUSTOMER'. The order information is then added to the database. The status of the order will be stored as 'PENDING'.
3. The shipping service staff of the shopping mall regularly view the pending orders and selects the ones to be shipped. The shipping service staff can also modify the quantity of the item to be shipped based on the quantity available in the inventory for that particular product. This action initiates an update operation to the database to modify the status of the order to 'IN_SHIPPING'.
4. The shipping process is automated and configured to run every six minutes (for demo purposes). The process scans the database for orders that are ready to be shipped (orders with status IN_SHIPPING ).
5. The first step of shipment is to bill the customer's account. This is handled by the Credit Agency Web Service which is invoked with the customer information and the amount of purchase.
6. The Web Service processes the information passed to it.
   • If the amount in the customer's account is sufficient then the account is charged with the amount and a success message is returned.
   • If the amount held in the customer's account is insufficient then the Web Service returns back a string 'INSUFFICIENT_FUNDS' and the customer's account is restored back to the original amount.
   • If the Web Service does not recognize the customer, it returns a fault with fault string "INVALID_CUSTOMER".
7. The database process updates the orders table with the response from the Web Service. When the payment is successful, the order status is changed from "IN_SHIPPING", to "SHIPPED". If the customer's account did not have sufficient funds, then the order status is changed to "INSUFFICIENT_FUNDS".

This marks the end of the order life cycle for this particular order. The user can view the status of the order any time. Also, the shiping staff intervention for updating the quantity to ship has been provided for visual effect purposes, the whole process could have been automated, with no human intervention, using the same stored procedure that handles the shipping process.

The architecture of this sample application is depicted in Figure 1.1.

The end user browses through the product catalog and places an order. The information supplied by the user is first validated by the Credit Agency Web Service and then added to the database. The shipping service staff of the shopping mall periodically view the pending orders. This request is handled by a Java Server Page which in turn looks up the database and displays the pending orders. The shipping service staff select orders to be shipped to the customers and determine the product quantity to be shipped based on the product availability in the inventory. This operation updates the order status as 'IN_SHIPPING'. A PL/SQL job runs in the database and is configured to run once every six minutes( for demo purposes ). This job executes a java stored procedure to charge the customer's account whose order is approved for shipping by the mall shipping service staff. The customer id and the amount of purchase are passed to this Stored function. The stored function packages all the required information and communicates with the Credit Agency Web Service. The Web Service charges the customers credit card account with the purchase amount and returns back a response based on the billing operation. The stored function updates the orders table with the value returned from the Web Service.

Java stored procedures are a powerful mechanisms for bridging SQL, Java, J2EE, XML and Web Services; for more details, refer to the white paper - Unleash the Power of Java stored procedures

    Figure 1.1- Application Architecture Diagram

The products catalog screen shown in Figure1.2 enables the user to browse through the different products available in the stores. The user can select any product and enter the quantity required. If no quantity is entered then it defaults to one. The users can specify their Customer ID and click on the Place Order button to initiate the purchase.

    Figure 1.2- Products Catalog Screen

The result of a purchase is shown in Figure 1.3. This screen is shown after the user is successfully validated by the Web Service.

    Figure 1.3 - Purchase Results Screen

The result of a purchase is shown in Figure 1.4. This screen is shown if the user has entered an invalid user id. The Web Service could not recognize the customer so returns back a fault string.

    Figure 1.4 - Purchase Results Screen

The orders listing screen is shown in Figure1.5. This enables the shipping service staff to browse through the different order placed in the stores. The shipping service staff selects any order for shipment and clicks on the Ship Order button to initiate the billing and shipment process.

    Figure 1.5- Orders Listing Screen

The response screen to the shipments requested by the shipping service staff is shown in Figure1.6. This displays the orders, customers and the status of the orders.

    Figure 1.6- Shipment Response Screen

The user can view the status of any order using the screen shown below. The user will need to enter the order ID that will be shown in Figure 1.3.

    Figure 1.7- View Order Status Screen

When the user enters a valid order id and clicks on the Get Order Details specified in Figure 1.7, the status of that particular order is displayed as shown below.

    Figure 1.8- View Order Status Response Screen

Pre-requisites

Back To Top

• Oracle9i Database version 9.0.1 or later. You can download the Oracle9i database from the Oracle Technology Network.
• Oracle9iAS Containers for J2EE (OC4J), 9.0.2 or later (available here ) along with JDK 1.2 or higher ( available here)
  or
  Oracle9i JDeveloper ( Version 9.0.3.988 Preview or later ) available here
• If you wish to deploy the application to standalone OC4J then you must download and install the Jakarta Ant utility version 1.4

The section has step-by-step instructions to run this application successfully.
This will be discussed in the following subsections

Notation

Extracting source code of the Application

Configuring the Application

Deploying the Application

Running the Application

Notation

• %SAMPLE_HOME%- The directory where the WSClientSample.jar file was unzipped to.
  
  
• %OC4J_HOME% - The directory where OC4J is installed. For example, if oc4j_extended.zip was unzipped to the C:\OC4J , then %OC4J_HOME% would be C:\OC4J.
  
  
• %ORACLE_HOME% - The directory where the Oracle9i Client or Database software is installed



• %ANT_HOME% - The directory where the Ant distribution has been unpacked.

Extracting source code of the Application

The application files are stored in an archive named WSClientSample.jar. Download the archive and extract it to a convenient directory (%SAMPLE_HOME%). Execute following command to extract the files:

jar xvf WSClientSample.jar

The archive WSClientSample.jar extracts the files that implement the Database as Web Service Client demo. All the files are extracted into the WSClientSample directory. This directory will have all the sources as described later.

Configuring the Application

1. This demo requires some database tables and sequences to be created in the database. The script for creating the required database objects and populating them with sample data is provided in the file WSCreate.sql located in the directory %SAMPLE_HOME%/WSClientSample/config.

Edit this file to specify your application server host and port number. You will need to modify this line :

execute dbms_java.grant_permission('WS','SYS:java.net.SocketPermission','<server-host-name>:<port>','connect,resolve');

For example,

execute dbms_java.grant_permission('WS','SYS:java.net.SocketPermission','incq184b.idc.oracle.com:8888','connect,resolve');

2. You need to execute the script WSCreate.sql. Connect to your database as any user and run the following command from the SQL prompt

SQL>@%SAMPLE_HOME%/WSClientSample/config/WSCreate.sql

This creates a new database user "WS" (password - "WS") with all the required tables and privileges.

3. Ensure that %ORACLE_HOME%\bin is in the system path. This demo requires some jar files to be loaded to the database. This is done from a command prompt as shown below :

loadjava -thin -user sys/<sys-password>@<hostname>:<port>:<SID> -resolve -synonym -verbose -grant public %OC4J_HOME%/soap/lib/soap.jar %OC4J_HOME%/lib/dms.jar %OC4J_HOME%/jlib/javax-ssl-1_1.jar %ORACLE_HOME%/lib/servlet.jar %OC4J_HOME%/jdk/jre/lib/ext/mail.jar

For example,

loadjava -thin -user sys/change_on_install@insn104a.idc.oracle.com:1521:otn9i -resolve -synonym -verbose -grant public c:\soap\lib\soap.jar c:\lib\dms.jar c:\jlib\javax-ssl-1_1.jar c:\orahome\lib\servlet.jar c:\jdk\jre\lib\ext\mail.jar

This will load all the required classes in the sys schema and create public synonyms for them.

Note : This step may take some time to complete.

4. Edit the file CreditAgencyServiceStub.java located in the directory %SAMPLE_HOME%/WSClientSample/src/oracle/otnsamples/wsclient. You need to change the web service endpoint with values specified in Step 1. Edit the following lines :

/*
* Change this variable to point to your web service end-point
*/
public String endpoint = "http://<server-host-name>:<port>/wsclient/CreditAgencyService";

For example,

public String endpoint = "http://incq184b.idc.oracle.com:8888/wsclient/CreditAgencyService";

5. Ensure that %ORACLE_HOME%\bin is in the system path. This demo requires the Web Service Client and stubs to be loaded to the database. This is done from a command prompt as shown below :

loadjava -thin -user ws/ws@<hostname>:<port>:<SID> -resolve -verbose %SAMPLE_HOME%/WSClientSample/src/oracle/otnsamples/wsclient/CreditAgencyServiceStub.java
%SAMPLE_HOME%/WSClientSample/src/oracle/otnsamples/wsclient/CreditAgencyServiceClient.java

where,

For example,

loadjava -thin -user ws/ws@insn104a.idc.oracle.com:1521:otn9idb c:\WSClientSample\src\oracle\otnsamples\wsclient\CreditAgencyServiceStub.java c:\WSClientSample\src\oracle\otnsamples\wsclient\CreditAgencyServiceClient.java

6. The Web Service client loaded in the previous step needs to be published and a database job has to be created to invoke the Web Service. This is done by the WSProcedure.sql

Connect to your database as any user and run the script WSProcedure.sql at the SQL prompt :
SQL>@%SAMPLE_HOME%/WSClientSample/config/WSProcedure.sql

This sql script publishes the stored procedure created in the previous step to the database and creates a job that invokes CreditAgencyServiceClient.java once every six minutes.

7.Edit Connection.properties file located in the directory %SAMPLE_HOME%/WSClientSample/config. Change the hostname, SID and Port to those of the database instance you want to connect to.

Change the following lines:

HostName	=	insn104a.idc.oracle.com
SID	=	otn9i
Port	=	1521
UserName	=	oe
Password	=	oe

Deploying the Application

The application can be deployed to an OC4J instance in one of the following two ways

Deploying the Application to Oracle9i JDeveloper's embedded OC4J

EAR( Enterprise Application aRchive ) Deployment

Deploying the Application to Oracle9i JDeveloper's embedded OC4J

1. Open Oracle9i JDeveloper.

2. Navigate to %SAMPLE_HOME%/WSClientSample

3. Open the file WSClientSample.jws and expand WSClientSample.jws node by clicking it.

4. Expand WSClientSample.jpr node by clicking it.

5. You can run the application from JDeveloper simply by clicking on Run WSClientSample.jpr on the Run Menu.

EAR(Enterprise Application archive) Deployment

1.Ensure that %ANT_HOME%\bin is in the system PATH. Also have OC4J_HOME environment variable point to %OC4J_HOME%. Use the following command from %SAMPLE_HOME%/WSClientSample to generate the EAR file -

ant -buildfile build.xml

2. Start OC4J .[ java -jar oc4j.jar from %OC4J_HOME%/j2ee/home]

3. Deploy the EAR from %OC4J_HOME%\j2ee\home using

java -jar admin.jar ormi://<host>:<port> <uid> <password> -deploy -file %SAMPLE_HOME%/WSClientSample/WSClientDemo.ear -deploymentName wsclient

This will deploy the application with name "wsclient"


Now, bind the web application to the web site using the following command,

java -jar admin.jar ormi://<host>:<port> <uid> <password> -bindWebApp wsclient WSClientDemo http-web-site /wsclient

where,

http-web-site	: the website to bind the web application
host	Host/ IP of this site
port	Port of this site
uid	: admin user id
password	: admin password

4. This completes the deployment to the standalone OC4J server. You can access the application following the steps given under Running the application.

Note : To undeploy the application use java -jar admin.jar ormi://localhost:<port> <uid> <password> -undeploy wsclient

Running the Application

1. Open a browser and type the URL to access the application. The URL should be of the following type

http://<server_host_name>:<port>/wsclient/Products.jsp
where <server_host_name> : The URL for the application Server. For ex. incq184b.idc.oracle.com
and <port> : Port No. on which the server is hosted. By default this value is 8888.

2. You can select any products to purchase.

3. For Customer ID you can specify otn-user or oracle-user.

4. The order should be placed successfully and should display a success message.

5. To view the list of pending orders, change the URL to

http://<server_host_name>:<port>/wsclient/ShipOrders.jsp

6. This will list all the pending orders. Select any order for shipping. If the quantity of any product requested is more than what is available in the inventory, then the requested quantity can be modified. This will update the order status to 'IN_SHIPPING'

7. Based on the amount of purchase and the credit balance of the customer, the status of the order will be updated when the batch job is run the next time. If you wish to see the next time the job will execute, connect to the database as ws/ws. Execute the following query at the SQL prompt:

SQL> SELECT last_date, last_sec, next_date, next_sec FROM user_jobs WHERE what LIKE 'ws_client%';

This will list the details about when the job was last executed and the date and time when it will execute next.

8. You could also query for the order status after the job is executed to view the response from the web service. For this, click on the View Order Status link provided in the header of the main page or open a browser and type the following URL -

http://<server_host_name>:<port>/wsclient/ViewOrders.jsp

 

Directory	Filename	Description
WSClientSample	build.xml	Project build file for generating deployment file using ANT
	WSClientSample.jws	Oracle9i JDeveloper workspace file
	WSClientSample.jpr	Oracle9i JDeveloper project file
WSClientSample/config	application.xml	Configuration file for the Application Server
	WSCreate.sql	The sql script used to create a database user and all the tables required by the sample application
	WSProcedure.sql	The sql script used to publish the java stored procedure to the database and create a PL/SQL batch job for invoking the java stored procedure
	Connection.properties	This file has the details of the database connection parameters
WSClientSample/doc	Readme.html	This file
WSClientSample/doc/images	GIF Files	Image files used within the Readme
WSClientSample/src/oracle/otnsamples/wsservices	CreditAgencyService.java	This class implements a simple stateless Java Web Service. It offers two basic functionalities - authorize customer and bill customer's creditcard account
	ICreditAgencyService.java	This is the Web Service interface which lists the methods exposed as web services.
	CreditAgencyService.wsdl	XML description of the web service
	ConnectionManager.java	Java Bean is used to manage database connection for the web service
WSClientSample/src/oracle/otnsamples/wsclient	CreditAgencyServiceClient.java	Java Stored Procedure that calls out the Credit Bureau Web Service to validate the user
	CreditAgencyServiceStub.java	Client side stub for the Credit Bureau Web Service. This is a local object that represents the remote service. This is generated from Oracle9i JDeveloper
	StoresBean.java	Java Bean is used by the Java Server Pages to handle all the database operations
	ProductsInfo.java	Value object used to pass products information between the Java Server Pages and the Java Bean
	OrdersInfo.java	Value object used to pass orders information between the Java Server Pages and the Java Bean
WSClientSample/webroot	Products.jsp	Retrieves product information from database and displays the catalog to the user. When the user places requests for products invokes the ProductsBean to add the order to the database
	ViewOrders.jsp	Retrieves order status for a specified order ID.
	ShipOrders.jsp	Retrieves order information from the database for the shipping service staff. When the shipping service staff select orders to be shipped, invokes ProductsBean to modify the order status
	ErrorHandler.jsp	Error Page for Java Servre Pages
WSClientSample/webroot/web-inf	web.xml	Configuration file for the Web Container

References

Back To Top

• Unleash the Power of Java stored procedures

Revision history: Oct 22, 2002

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.