How to deploy a PL/SQL function with Boolean input as Web Service

Date: 2-Jan-2003

After completing this how-to you should be able to:

  • Publish a PL/SQL function that takes a Boolean as a Web Service
  • Use Web Services Assembler to create an Enterprise Application aRchive (EAR) file for deployment to OC4J

Software Requirements

  • Oracle9i Database version 9.0.1 or later with sample schema installed. You can download the Oracle9i database from Oracle Technology Network.

  • Oracle9iAS Containers for J2EE (OC4J), 9.0.3 or later (available here ) along with JDK 1.2 or higher ( available here)

  • Sun's JDK 1.3_01 or above, available here

Notation

  • <SAMPLE_HOME>- The directory where the Boolean.jar file was unzipped to. This directory contains the Boolean directory.
  • <ORACLE_HOME> - The directory where the Oracle9i Client or Database software is installed

Introduction

This document describes how to publish a PL/SQL function that takes a Boolean input as a Web Service. Boolean is not a supported JDBC type. Accessing Boolean type from Java using JDBC is not possible as of now. So a mapping of Boolean to another SQL type that is understood by JDBC is required. Integer type is understood by JDBC and PL/SQL and is also a supported type by the Web Service framework. This conversion is actually already pre-programmed in JPublisher (just make sure that the SQL file <ORACLE_HOME>/sqlj/lib/sqljutl.sql has been installed in the database). This helps us to avoid the creations of 2 functions that does the mapping from Boolean to Integer and vice versa.

The Web Service that is to be created for this demonstration is a simple tax calculator for the Employees. Here we use the EMPLOYEES Table in the HR/HR user of the sample Schema provided along with Oracle 9i database. The scenario we assume is that the taxes are different for singles (30%) and married employees (20%). A PL/SQL procedure is created which takes as inputs the Employee ID and a boolean that specifies if the employee is married or not and returns the calculated tax. This procedure is published as Web Service.

Extracting the source code

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

>jar xvf Boolean.jar

This creates the 'Boolean' directory and extracts the following files and directories :

  • config/- contains configuration files.

    • CreateProcedure.sql - The sql script used to create the PL/SQL function
    • config.xml - XML file containing the inputs for Web Services Assembler
  • doc/

    • boolean.html- This document.

Steps for deploying the PL/SQL function as Web Service

The following are the sequence of steps required to publish the PL/SQL function as a Web Service.
1.

Create the PL/SQL function by executing the file CreateProcedure.sql located in the directory <SAMPLE_HOME>/Boolean/config.

To execute the script CreateProcedure.sql,connect to your database as hr/hr and run the following command from the SQL prompt

SQL>@<SAMPLE_HOME>\Boolean\config\CreateProcedure.sql

This creates a new package EMPTAX with a function getTax.

2.

Now you can use the Web Services Assembler to create a deployable Web Service EAR file.
The WebServicesAssembler.jar is present at <OC4J_HOME>\webservices\lib

You need to provide inputs to the WebServicesAssembler.jar through a configuration file config.xml. A sample config.xml is present in the <SAMPLE_HOME>\Boolean\config.xml. You can copy this config.xml to <OC4J_HOME>\webservices\lib.

The config.xml contains information about the database username\password database URL, the package that implements the webservice,etc.

<!-- Connection information -->
<schema>hr/hr</schema>
<db-url>jdbc:oracle:thin:@<host>:<port>:<sid>
</db-url>
<!-- PL/SQL package information. By default, this is also used for the Java class name -->
<db-pkg-name>EmpTax</db-pkg-name>

Make sure to change the database URL to the one where you have created the PL/SQL function. This information is used only at the package creation time. When the application runs, it requires a JNDI data source that connects to the database.
The JNDI data source is provided in the config.xml file as shown below.

<!-- Info needed at runtime - the JNDI DB connection -->
<database-JNDI-name>jdbc/ora9idb</database-JNDI-name>

Make sure to change the data source name to the one that connects to the EMPTAX package in the HR user of sample schema. If the data source does not exist, you can provide a new data source name by creating the following entry in the <OC4J_HOME>\j2ee\home\config\data-sources.xml.

<data-source
class="oracle.jdbc.pool.OracleDataSource"
name="ora9idb"
location="jdbc/ora9idb"
xa-location="jdbc/xa/ora9idbXADS"
ejb-location="jdbc/ora9idb"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="hr"
password="hr"
url="jdbc:oracle:thin:@<hostname>:<port>:<sid>"
inactivity-timeout="30"
/>

Note: Add valid values for hostname, port and SID

3.

Now give the following command to generate the EAR file from <OC4J_HOME>\webservices\lib

>java -jar WebServicesAssembler.jar -config config.xml

This will create the EmpTax.ear file.

4.

Copy the above created ear file to: <OC4J_HOME>\j2ee\home\applications directory.

5.

Start the OC4J server. For this, go to <OC4J_HOME>\j2ee\home folder and type the following

>java -jar oc4j.jar
6.

Deploy the EAR from <OC4J_HOME>\j2ee\home using

>java -jar admin.jar ormi://<server>:<rmi-port> admin <password> -deploy
-file <OC4J_HOME>\webservices\lib\EmpTax.ear -deploymentName EmpTax

This will deploy the application with name "EmpTax"

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

>java -jar admin.jar ormi://<server>:<rmi-port> admin <password>
-bindWebApp EmpTax EmpTax_web http-web-site /Boolean

where,
http-web-site The website to bind the web application
server Host/ IP of this site
rmi-port RMI port of this site
password admin password for the OC4J server

7.

You can see the working of the Web Service by opening a browser and going to the following URL

http://<server>:<http-port>/Boolean/EmpTax

You will see a list of the operations of your Web Service and be able to test operations individually through your browser.

The endpoint of the Web Service is

http://<server>:<http-port>/Boolean/EmpTax?WSDL

where,
server Host/ IP of this site
http-port Http port of this site


Summary

After going through this document you should have understood how to deploy a PLSQL function which takes a Boolean input as a Web Service using Web Services Assembler

References

1. For information on how to use JDeveloper to deploy a PLSQL function as a Web Service see /tech/webservices/htdocs/series/plsql/index.html

2. For information on Oracle9i database and Web Services see /tech/webservices/database.html


E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy