How to Expose Custom PL/SQL API as a REST Webservice?
Oracle E-Business Suite Integrated SOA Gateway (ISG) provides an infrastructure to deploy, consume, and administer Oracle E-Business Suite web services. It service-enables public integration interfaces registered in Oracle Integration Repository. These interfaces can be deployed as SOAP and/or REST-based web services. ISG also provides a service invocation framework to consume external SOAP based web services.
Steps to expose/register Custom PL/SQL API as a REST API.
1.Create a Custom Package and annotate it
Create a custom package. Save the package specification as .pls file and annotate only the package specification.
Eg: XX_INVENTORY_PKG1.pls
You can create either procedure or functions depending on your requirements.
Functions with Return Parameter Table Type is helpful for getting output from a query.
create or replace PACKAGE XX_INVENTORY_PKG1 AS
/* $Header: $ */
/*#
* This custom PL/SQL package can be used to get inventory item details
* @rep:scope public
* @rep:product INV
* @rep:displayname XX_INVENTORY_PKG1
* @rep:category BUSINESS_ENTITY INV_ITEM
*/
/*#
* Get Inventory Item Details
* @param p_organization_id number
* @param p_item_code VARCHAR2
* @return inventory table
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Inventory Item Details
*/
FUNCTION GET_INVENTORY_DETAILS(P_ORGANIZATION_ID IN NUMBER,P_ITEM_CODE IN VARCHAR2)
return XX_ITEM_DETAILS_TAB;
END XX_INVENTORY_PKG1;
XX_ITEM_DETAILS_TAB –This is a type
2. Upload the pls file into the EBS Server.
Move the xx_inventory_pkg1.pls file to the respective product TOP folder.
Eg: $INV_TOP/patch/115/sql
3. Generate the iLDT file (Integration Repository loader file)
Run the below commands to generate the iLDT file (Integration Repository loader file) and the commands should be run in the same directory as where .pls file was copied to. The iLDT file is uploaded to the Integration Repository. The file is created with a tool called Integration Repository Parser (IREP Parser), the tool will validate the file against the annotation standards.
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin inv:patch/115/sql:XX_INVENTORY_PKG1.pls:12.0=XX_INVENTORY_PKG1.pls
4. Upload the iLDT file to Integration Repository by running the below command.
$FND_TOP/bin/FNDLOAD <db_user>/<db_password> 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XX_INVENTORY_PKG1_pls.ildt
Replace the <db_user> and <db_password> with database apps user and password.
5. Create the package with the package body in the database and compile.
6. Register the Custom PL/SQL API in the Oracle EBS ISG(Integrated SOA Gateway)
- Login into Oracle EBS application.
Make sure the user is assigned to Integration SOA Gateway Responsibility.
Also, the user should be assigned the “Irep Administrator” .
- Navigate to Integration SOA Gateway Responsibility. Click Search.
- Enter “XX_INVENTORY_PKG1” on the search page in the Internal Name and click on Go
- Click on the Name Link, it will display List of method available in the Interface Package.
- Click on REST Web Service Tab
- Enter Service Alias and then Click on Deploy button at bottom.
- You will receive message that service is successfully deployed and the status will be deployed.
- Now Click on the Grants Tab, select the object and then click on Create Grant button
- Select a Grantee Type and Grantee Name
Click on Create Grant Button.
- To Revoke Grants, click on Revoke Grants button under Grants Tab. List of users with access will be displayed for revoking the Grants.
- To Undeploy the Web services select the method and click on undeploy under the REST Web Services Tab.
- It is important to grant permissions to newly generated interface (REST webservice). Grant section allow administrators to restrict the interface access to individuals or specific groups or to everyone in an organization.
- Click on View WADL link in the Rest Web Service Tab to see the REST endpoint.
- Before invoking the webservice, clear the cache.
Use responsibility Functional Administrator and go to Core Services -> Caching Framework -> Global Configuration -> Clear all cache.
- The webservice is ready to be deployed.
- We can test the REST service via POSTMAN tool. Below would be the endpoint of REST service.
https://localhost/webservices/rest/InvData/GET_INVENTORY_DETAILS/
Add the BASIC authentication for testing using the postman.
