Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Working with PL/SQL Web Service in JDeveloper 12c

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,
- I have created a sample PL/SQL package using DB Explorer in JDeveloper.
- Package has been exposed as a Web Service and tested using HTTP Analyzer.
- 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...
- Open DB Explorer.
- Create a DB connection.
Note: Make sure HR schema is unlocked. Refer to Unlock HR Schema for more detail.
- Open SQL Worksheet to run SQL scripts.
- 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;
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
Create custom application.
- Specify application name and package structure.
- Select Project Features “SOAP Web Service” and add it to the selected by clicking ">"
Click “Finish” to create application.
- To publish SQL Package as TOPLink DB Web Service Provider, Right Click on Project-> Click “From Gallery”
- Select TOPLink DB Web Service Provider and click OK.
- Go through the Web Service Provider Wizard
- Select PL/SQL Web Service Provider. (We are using SQL package to expose it as a Web Service.) Click Next.
- Specify required properties: Database Connection Name, Database Package, Web Service Name, etc. Click Next.
- Select the function that needs to be exposed as service.
Figure 14
- Click Finish. It will take a while to generate Web Service using Package.
Testing PL/SQL Web Service
- Right click on DBWSProvider.java.
- Select Test Web Service from the pop-up menu.
- 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
Create new ADF Fusion Web Application.
Specify Application Name and package structure.
Click Next and then Finish. An ADF Web Application will generate.
Create New Web Service Data Control using copied WSDL.
Create New Data Control. Select Web Service Data Control using SOA/REST.
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.
Select Operation.
Select Response Format.
Click Finish to generate Data Control out of WSDL.
Create New JSF Page
Create JSF page.
Add one PanelGroupLayout and one panel box to it.
Expand DataControl and drag usdAmt as inputText. Change label to “Amount in USD.”
Drag BigDecimal as outputText. Change label to “Amount in INR.”
Drag Data Control as method->AdfButton. Change label to “Convert.” .Change ActionListener to Action for AdfButton.
- 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.
Comments
-
Feel free to ask a question around this.
-
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
-
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
-
Hi Kumar,
Its helpful post.
Thanks & Regards,
Guravaiah Tata.
-
Hi Kumar,
Its helpful post.
Thanks & Regards,
Guravaiah Tata.
Thanks Guru!
-
Hi Kumar,
This is very well explained and helpful
Thanks
Namit.
-
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.
-
Hi Kumar,
How do I add a security header to the web service that validates from a users' table?
Thanks,
Fede.
-
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
-
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