Java Development Tools

By Kumar Abhishek Shahi

Overview

Numerous legacy systems that have Procedural Language/Structured Query Language (PL/SQL) code containing complex business logic are in migration. The business need is to reuse them and create an interface to expose them to various other systems..

Publishing the PL/SQL package as a Web Service is a simple solution to such scenarios:

• It reduces development effort because existing code can be re-used.
• The complex logic which has been put in the PL/SQL code which has been running over years and tested should not be re-written.
• Web Services built on top of it can be consumed by any application i.e. Web Application, Desktop Application, Mobile Interface as well as Portals and other Packaged Applications.

This Article focuses on:

• How we can publish a PL/SQL Package as Web Service using JDeveloper 12c.
• How we can consume those services in an ADF Application.

Pre-requisites

• JDeveloper 12c
• Oracle Database XE
• Basic PL/SQL knowledge

Use Case

I have taken a simple use case of converting USD Amount to INR Amount.

In this exercise,

1. I have created a sample PL/SQL package using DB Explorer in JDeveloper.
2. Package has been exposed as a Web Service and tested using HTTP Analyzer.
3. At the end, there is an ADF Web Application which consumes Web Service and displays it a beautiful UI using JSF Page.

Solution

Creating a PL/SQL Package to Convert USD to INR

In JDeveloper...

1. Open DB Explorer.

   

2. Create a DB connection.

   

   Note: Make sure HR schema is unlocked. Refer to Unlock HR Schema for more detail.

3. Open SQL Worksheet to run SQL scripts.

   

4. Create a package utility with a function to convert USD to equivalent INR amount.

   

   create or replace PACKAGE Utility AS

   FUNCTION UsdToInr(usdAmt number)

   return number;

   END Utility;

5. Create package and function body.

   

   create or replace PACKAGE BODY Utility AS

   FUNCTION UsdToInr(usdAmt IN number)
   
   RETURN number IS
   
      inrAmt number(5) := 0;
   
   BEGIN
   
      SELECT usdAmt\*60 into inrAmt from dual;
   
      RETURN inrAmt;
   
   END UsdToInr;
   

   END Utility;

Publishing Package as a Web Service

1. Create custom application.

   

2. Specify application name and package structure.

   

3. Select Project Features “SOAP Web Service” and add it to the selected by clicking ">"

   

4. Click “Finish” to create application.

5. To publish SQL Package as TOPLink DB Web Service Provider, Right Click on Project-> Click “From Gallery”

   

6. Select TOPLink DB Web Service Provider and click OK.

   

7. Go through the Web Service Provider Wizard

   

8. Select PL/SQL Web Service Provider. (We are using SQL package to expose it as a Web Service.) Click Next.

   

9. Specify required properties: Database Connection Name, Database Package, Web Service Name, etc. Click Next.

   

10. Select the function that needs to be exposed as service.

![image014.jpg](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/2/8/2/28222154egami.jpeg)Figure 14

11. Click Finish. It will take a while to generate Web Service using Package.

Testing PL/SQL Web Service

1. Right click on DBWSProvider.java.

   

2. Select Test Web Service from the pop-up menu.

   

3. HTTPAnalyzer will open. Enter USDAmount = 50 and click “Send Request.”

   

   You can see that the result is displayed as 3000.

   Copy the Web Service Definition Language (WSDL) URL for future use:

   http://localhost:7101/SQLFunctionAsService-Project1-context-root/UsdToInrService?WSDL

Creating User Interface to Consume Web Service

1. Create new ADF Fusion Web Application.

   

2. Specify Application Name and package structure.

   

3. Click Next and then Finish. An ADF Web Application will generate.

4. Create New Web Service Data Control using copied WSDL.

5. Create New Data Control. Select Web Service Data Control using SOA/REST.

   

6. Specify DC Name and WSDL. Check Copy WSDL Locally so that there is no need to hit the server to see whether WSDL is readable or not.

   

7. Select Operation.

   

8. Select Response Format.

   

9. Click Finish to generate Data Control out of WSDL.

Create New JSF Page

1. Create JSF page.

   

2. Add one PanelGroupLayout and one panel box to it.

   

3. Expand DataControl and drag usdAmt as inputText. Change label to “Amount in USD.”

4. Drag BigDecimal as outputText. Change label to “Amount in INR.”

5. Drag Data Control as method->AdfButton. Change label to “Convert.” .Change ActionListener to Action for AdfButton.

   

6. Save and run!

Output

Summary

Exposing PL/SQL Package as Web Service is a proven solution which could be achieved using JDeveloper 12c. JDeveloper 12c enables developers to generate and consume Web Service from one IDE. PL/SQL Web Services offers greater flexibility, scalability and reuse of existing resources.

About the Author

Kumar Abhishek Shahi is a principal consultant at Oracle Corporation, Working as an Architect for Webcenter Portal and ADF-based solutions, including technical architecture evaluation, performance tuning, code review, creating POCs and developing full-fledged solutions.