Working with PL/SQL Web Service in JDeveloper 12c

Version 6

    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.

      image001.jpg

    2. Create a DB connection.

      image002.jpg

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

    3. Open SQL Worksheet to run SQL scripts.

      image003.jpg

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

      image004.jpg

      create or replace PACKAGE Utility AS
         FUNCTION UsdToInr(usdAmt number)
         return number;
      END Utility;
      
      
      
      
      

       

    5. Create package and function body.

      image005.jpg

       

      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.

      image006.jpg

    2. Specify application name and package structure.

      image007.jpg

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

      image008.jpg

    4. Click “Finish” to create application.

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

      image009.jpg

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

      image010.jpg

    7. Go through the Web Service Provider Wizard

      image011.jpg

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

      image012.jpg

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

      image013.jpg

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

      image014.jpgFigure 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.

      image015.jpg

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

      image016.jpg

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

      image017.jpg

      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.

      image018.jpg

    2. Specify Application Name and package structure.

      image019.jpg

    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.

      image020.jpg

    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.

      image021.jpg

    7. Select Operation.

      image022.jpg

    8. Select Response Format.

      image023.jpg

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

     

    Create New JSF Page

     

    1. Create JSF page.

      image024.jpg

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

      image025.jpg

    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.

      image026.jpg

    6. Save and run!

     

    Output

     

    image027.jpg

     

    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.