OracleApps02: Exposing Pl/Sql as REST Web Service (Function Returning single value)

Perform following steps to expose Pl/SQL as a web service

1. Create a Custom Package ,annotate it

2. Create iLDT File

3. Upload the iLDT File to Integration Repository.

4. Search API in Integration Repository

5. Deploy API as REST Service

6. Create Grants for REST Service

7. Invoke EBS REST Service (in JSON message format)

8. Verify the results

Here we will take an example of Getting Employee Number based on Employee details provided when invoking the Web service.

Following steps will explain the process of exposing the Custom API that returns varchar variable as output when invoked.

Step 1: First Create a custom package xx_test_pkg with .pls extension and annotate it Only annotate the package specification. Below code was used for this Case study.

Package Specification:

/* Formatted on 19/7/2023 4:51:45 PM (QP5 v5.360) */

CREATE OR REPLACE PACKAGE xx_test_pkg

/* $Header: $ */

/*#

* This interface returns the Employee Number.

* @rep:scope public

* @rep:product PER

* @rep:displayname xx_test_pkg

* @rep:lifecycle active

* @rep:compatibility S

* @rep:category BUSINESS_ENTITY PER_EMPLOYEE

/*#

* Returns Employee Number

* @param P_First_name VARCHAR2 First Name

* @param P_Last_name VARCHAR2 Last Name

* @param P_Email_address VARCHAR2 Email Address

* @return Employee_Number

* @rep:scope public

* @rep:lifecycle active

* @rep:displayname Return Employee Number

FUNCTION xxget_emp_number (p_first_name IN VARCHAR2,

p_last_name IN VARCHAR2,

p_email_address IN VARCHAR2)

RETURN VARCHAR2;

END xx_test_pkg;

Package Body

CREATE OR REPLACE PACKAGE BODY xx_test_pkg

FUNCTION xxget_emp_number (p_first_name IN VARCHAR2,

p_last_name IN VARCHAR2,

p_email_address IN VARCHAR2)

RETURN VARCHAR2

xxreturn VARCHAR2 (30);

BEGIN

SELECT 1

INTO xxreturn

FROM per_all_people_f

WHERE first_name = p_first_name

AND last_name = p_last_name

AND email_address = p_email_address;

RETURN xxreturn;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

RETURN 'No Data Found';

WHEN OTHERS

THEN

RETURN 'Invalid Data';

END xxget_emp_number;

END xx_test_pkg;

Step 2: Upload the .pls file to Oracle server.For our case study file was uploaded to custom_top Bin folder

Step 3: Create iLDT file by using following command and current Directory is where the .pls file was uploaded :

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin PER:patch/115/sql:xx_test_pkg.pls:12.0=xx_test_pkg.pls

Following message will appear after executing the command and it will generate ilDT file.

Step 4: Upload the generated iLDT file to Integration repository by using following command:

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xx_test_pkg_pls.ildt

Step 5: Navigate to Integration Repository Responsibility, Click on Search.

Then on search Page enter “xx_test_pkg” in Internal Name and click on Go

Step 6: Click on the search result Link, it will display List of method available in the Interface Package.

Click on REST Web Service Tab

This page will display the Return Employee Number method.

Step 7: Now select Return Employee Number method ( we will create a user by invoking this Service once Deployed.) and enter Service Alias (This is mandatory) and then Click on Deploy button at bottom. You will receive message that service is successfully deployed and the status will be deployed.

Step 8: Now you will see the link to WADL file , Click on the link . It shows the physical location of the service endpoint where the service is hosted.

<?xml version="1.0" encoding="UTF-8"?>

<include xmlns="http://www.w3.org/2001/XMLSchema" href="http://xxx-xxx-

apdv01.cli.ad:8005/webservices/rest/Get_Emp_Num/?XSD=GET_EMP_NUMBER_SYNCH_TYP EDEF.xsd"/></grammars>

<resources base="http://xxx-xxx-

ap01.xx:8000/webservices/rest/Get_Emp_Num/"><resource path="get_emp_number/"><method name="POST" id="GET_EMP_NUMBER"><request><representation type="tns1:InputParameters" mediaType="application/xml"/><representation type="tns1:InputParameters" mediaType="application/json"/></request><response><representation type="tns1:OutputParameters" mediaType="application/xml"/><representation type="tns1:OutputParameters" mediaType="application/json"/></response></method></resource></resources></ap plication>

Now open the the link highlighted in Yellow in new Browser window, it will show the input parameters that will be used later for invoking the Web Service.

Step 9.Now Click on the Grants Tab, select return Employee Number and then click on Create Grant button

Step 10: Select a grantee type. In our Case Study we will enter SYSADMIN and then click on Create Grant Button.

It will display confirmation message:

To Revoke Grants click on Revoke Grants button under Grants Tab, it will display List of users with access for revoking the Grants.

To Undeploy the Web services select the method and click on undeploy under the REST Web Services Tab

Step 11: Now we will invoke this web service using Advanced REST Client apps available in Google Chrome.

Please refer to the steps from case study 1 to invoke the web service. For this case study following details were used:

Link:- http://xxx-xxx-ap01.xx:8000/webservices/rest/Get_Emp_Num/get_emp_number/

Payload:

{

"Get_Employee_Number": {

"@xmlns": "http://xmlns.oracle.com/apps/per/rest/Get_Emp_Num/get_emp_number", "RESTHeader": {

"xmlns": "http://xmlns.oracle.com/apps/per/rest/Get_Emp_Num/header", "Responsibility":"US_HRMS_MANAGER",

"RespApplication":"PER", "SecurityGroup":"STANDARD", "NLSLanguage":"AMERICAN", "Org_Id" :"201"

"InputParameters": { "P_FIRST_NAME": XX", "P_LAST_NAME": "XX"

}

Step 12: Enter the payload information and click on send. You will receive attached response.

{

"OutputParameters" : {

"@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",

"@xmlns" :

"http://xmlns.oracle.com/apps/per/rest/Get_Emp_Num/get_emp_number/", "GET_EMP_NUMBER" : "1"

}