Working with PL/SQL Web Service in JDeveloper 12c

Bob Rhubart-Oracle

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.
    

    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.

Abhishekshahi-Oracle

Feel free to ask a question around this.

2797856

Feel free to ask a question around this.

Hi Abhishek,

Is it only applicable for PL-SQL functions or PL-SQL procedures as well. Can I do this for an Oracle Apps Procedure which is inside a Package.

Regards,

Nikhil

Abhishekshahi-Oracle

Hi Abhishek,

Is it only applicable for PL-SQL functions or PL-SQL procedures as well. Can I do this for an Oracle Apps Procedure which is inside a Package.

Regards,

Nikhil

Hi Nikhil,

It is applicable for functions and procedures both with certain limitations. Refer - https://docs.oracle.com/cd/E35521_01/user.111230/e17455/dev_web_services.htm#OJDUG1293 for more details.

Yes, you can do it for Oracle Apps Procedures also which are inside packages but you should be aware that it doesn't support refcursor as return type. more limitations etc details are in the provided link.

Let me know if you need further help.

Thanks,

-Kumar

Guravaiah Tata

Hi Kumar,

Its helpful post.

Thanks & Regards,

Guravaiah Tata.

Abhishekshahi-Oracle

Hi Kumar,

Its helpful post.

Thanks & Regards,

Guravaiah Tata.

Thanks Guru!

Namit Kakkar-Oracle

Hi Kumar,

This is very well explained and helpful

Thanks

Namit.

2807810

Feel free to ask a question around this.

Hi Abhishek,

What do I need to change (or add) to generate a REST web service instead?

Thank you.

Fede.

2807810

Hi Kumar,

How do I add a security header to the web service that validates from a users' table?

Thanks,

Fede.

Abhishekshahi-Oracle

Hi Abhishek,

What do I need to change (or add) to generate a REST web service instead?

Thank you.

Fede.

Hi Fede,

There is no mechanism to generate Rest-ful services on PL/SQL code directly but you can write a light java client on top of PL/SQL code and publish that client as restful service. Please find below link for creating Restful WebServices,

https://docs.oracle.com/cd/E35521_01/user.111230/e17455/dev_web_services.htm#OJDUG1400

Hope it helps.

Regards,

-Kumar

Abhishekshahi-Oracle

Hi Kumar,

How do I add a security header to the web service that validates from a users' table?

Thanks,

Fede.

Hi Fede,

Adding Security to the services are explained here using JDeveloper. https://docs.oracle.com/middleware/1212/jdev/OJDUG/dev_web_services.htm#OJDUG1293

Let me know if you need further help.

Regards,

-Kumar