Expose custom PL SQL APIs as a SOA Gateway Webservice
Integrated SOA Gateway(ISG) is an integrated component of E-Business Suite R12. ISG allows to expose EBS functionality(which includes XML gateway, Business Service Objects, Concurrent Programs, PL/SQL API’s, Business Events, E Commerce Gateway, Open Interface Tables/Views etc.) as a SOAP/REST WebServices.
To expose a PL/SQL package as a service interface, it is mandatory to annotate the package with standard annotation.
Below steps to be followed to expose a PL/SQL package as a REST interface.
- PL/SQL package is annotated with the standard annotation
create or replace PACKAGE XX_SAVE_EMPLOYEE AS
/* $Header: $ */
/*#
* This custom PL/SQL package can be used to insert employees
* @rep:scope public
* @rep:product per
* @rep:displayname XX_SAVE_EMPLOYEE
* @rep:category BUSINESS_ENTITY PER_EMPLOYEE
*/
/*#
* Save records in emp table
* @param c_id varchar2
* @param name varchar2
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname save_employee
*/
PROCEDURE save_employee_PRC(c_id IN VARCHAR2,
name IN VARCHAR2);
END XX_SAVE_EMPLOYEE;
Preceded package contains a single procedure save_employee_PRC that takes two input parameters. This procedure is used to insert data(c_id and name) into the table.
Note: For PL/SQL packages, only the package spec should be annotated. Do not annotate the body.
- Create a .pls file and paste the above code in the .pls file. Move the .pls file to the EBS server via winscp or putty. In this case we will copy the file at $APPL_TOP/patch/115/sql
- Next step is to create an iLDT file (Integration Repository loader file) that we should use to upload 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.
- Run the below command to generate the iLDT file
[root@testmachine]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadminper:patch/115/sql:XX_SAVE_EMPLOYEE.pls:12.0=XX_SAVE_EMPLOYEE.pls
Note: If you are generating a new iLDT file for an already uploaded interface we need to add a higher version number then the last uploaded.
[root@testmachine]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadminper:patch/115/sql:XX_SAVE_EMPLOYEE.pls:12.1=XX_SAVE_EMPLOYEE.pls
If everything OK below would be the output of the above command
# Interface Repository Annotation Processor, 12.0.0
#
# Generating annotation output.
# Processing file ‘XX_SAVE_EMPLOYEE.pls’.
# Using YAPP-based parser.
# Found a package-level annotation for ‘XX_SAVE_EMPLOYEE’.
# Found a detail-level annotation…
# Found a procedure named ‘SAVE_EMPLOYEE_PRC’.
# Done all files.
Please Note: XX_SAVE_EMPLOYEE_pls.ildt would be generated.
- Next is used to upload the generated iLDT file to integration repository. Run below command to upload the iLDT file to integration repository
[root@testmachine]$ $FND_TOP/bin/FNDLOAD <db_user>/<db_password>0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XX_SAVE_EMPLOYEE_pls.ildt
Note: Replace the <db_user> and <db_password> with database apps user and password.
Above command will generate a .log file. Just tail the file and see the below success output
[root@testmachine]$ tail -500f L2602439.log
+—————————————————————————+
Application Object Library: Version : 12.2
Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.
FNDLOAD: Generic Loader
+—————————————————————————+
Current system time is 15-NOV-2017 23:17:58
+—————————————————————————+
Uploading from the data file XX_SAVE_EMPLOYEE_pls.ildt
Altering database NLS_LANGUAGE environment to AMERICAN
Dump from LCT/LDT files
(/appl/fnd/12.0.0/patch/115/import/wfirep.lct(120.8.12020000.3), XX_SAVE_EMPLOYEE_pls.ildt) to stage tables
Dump LCT file
/appl/fnd/12.0.0/patch/115/import/wfirep.lct(120.8.12020000.3) into FND_SEED_STAGE_CONFIG
Dump LDT file XX_SAVE_EMPLOYEE_pls.ildt into FND_SEED_STAGE_ENTITY
Dumped the batch (IREP_OBJECT PLSQL:XX_SAVE_EMPLOYEE C , PARAMS 2 0 ) into FND_SEED_STAGE_ENTITY
Upload from stage tables
+—————————————————————————+
Concurrent request completed successfully
Current system time is 15-NOV-2017 23:17:59
- Now create the package and package body in the database with the below script
Package
create or replace PACKAGE XX_SAVE_EMPLOYEE AS
/* $Header: $ */
/*#
* This custom PL/SQL package can be used to insert employees
* @rep:scope public
* @rep:product per
* @rep:displayname XX_SAVE_EMPLOYEE
* @rep:category BUSINESS_ENTITY PER_EMPLOYEE
*/
/*#
* Save records in emp table
* @param c_id varchar2
* @param name varchar2
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname save_employee
*/
PROCEDURE save_employee_PRC(c_id IN VARCHAR2,
name IN VARCHAR2);
END XX_SAVE_EMPLOYEE;
Package body
create or replace PACKAGE BODY XX_SAVE_EMPLOYEE AS
PROCEDURE save_employee_PRC(c_id IN VARCHAR2,
name IN VARCHAR2) IS
BEGIN
INSERT INTO xx_test(id,name) values(c_id,name);
END save_employee_PRC;
END XX_SAVE_EMPLOYEE;
- Now, login into EBS console and Navigate to the Integrated SOA Gateway -> Integration Repository
- Enter the display name(XX_SAVE_EMPLOYEE) in Internal Name and click on Search button
- Click on the XX_SAVE_EMPLOYEE and switch to REST Web Service tab.
- Enter Service Alias: This would be the service name
- Select save_employee Checkbox and click Deploy button
Once successfully deployed, success message will be displayed
- Switch to Grants tab, select Checkbox and click on Create Grant button
- Select Specific User from the Grantee Type drop down and enter Grantee Name to give access to specific user to execute this service. Click on Create Grant button
- Once done, success message will appear on the screen
Now the PL/SQL has been exposed as a REST interface
Click on View WADL link to see the REST endpoint.
We can test the REST service via POSTMAN tool. Below would be the endpoint of REST service.
During testing, add the BASIC authentication.
This really has covered a great insight on PL Sql . I found myself lucky to visit your page and came across this insightful read on PL Sql tutorial. Please allow me to share similar work on Pl sql training course:-
https://www.youtube.com/watch?v=phvcwekT9ZA
Thanks for sharing Good Information
Oracle SOA Online Course
Nice Post I learned a lot From the Post Thanks for sharing, learn the most ON-DEMAND software Training in Best Training Institutions
Instructor-LED Salesforce Online Training
Salesforce certification Training program
Professional Salesforce CRM Training
your blog explains everything thanks sql and pl sql Online training bangalore
Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com
Hi Ankur,
Can't we use PL/SQL pkgs directly using E-Business Suite Adapter in OIC? Is it mandatory to expose as API's and use in Adapter?
Thanks,
Lakshman.
Hi Lakshman,
No you can't call PL/SQL packages from E-Business adatper. Leverage DB adapter to call pl/sql packages
This comment has been removed by the author.
Thanks for reply , We have a requirement where OIC has to integrate with Oracle E-Business Suite for Inbound/Outbound Operations. I am using EBS Suite Adapter because it is already installed in Cloud IAAS. As it is in cloud i have only one option that is use E-Business Suite Adapter.
My EBS system is in cloud IAAS can i use database adapter to connect or if yes how to connect ??
Thanks,
Lakshman.
You need to install an OIC Agent on IAAS to make DB connectivity using OIC DB adapter.
Hi Ankur,
Which one is feasible to connect with EBS(Iaas) system through E-Business Suite Adapter or Data base Adapter ??
Thanks,
Lakshman.
Both are feasible bur preferred is EBS adapter
Thank you very much Ankur. I have one more question if my EBS is in cloud with private network in that case does ISG work to consume from OIC instance or any extra setup need to do ?
Thanks,
LAkshman.
Thanks,
Lakshman.
This comment has been removed by the author.
Thankyou for sharingerp software companies
The combination of both SQL and SOAP API make up for solving some very complex IT problems and hence its aspects should be judiciously utilized.
SQL Server Load Soap Api