REST in PL/SQL using Integrated SOA gateway

February 14,2017

Oracle E-Business Suite Integrated SOA Gateway (ISG) is an Out-of-the-Box module that enables supported interface types published in Oracle Integration Repository,These interfaces can be transformed into SOAP and REST web services. Please refer metalink note number (Doc ID 1311068.1): for Installing Oracle E-Business Suite Integrated SOA Gateway in Release 12.2.
High Level Configuration Steps:

1)Enabling ASADMIN User with the Integration Administrator Role
(ASADMIN should be active user and with complex password and should have UMX|FND_IREP_ADMIN role.)
2)Create a folder called ISG_TEMP in Oracle E-Business Suite. This folder should have write permission.
3)In the $INST_TOP\soa folder, update ISG_TEMP_DIRECTORY_LOCATION property in the isgagent.properties file as follow:SID.ISG_TEMP_DIRECTORY_LOCATION=ISG_TEMP.
4)Run the txkISGConfigurator.xml utility with “ebsSetup” argument. The script will stop and restart the servers.
ant -f $JAVA_TOP/oracle/apps/fnd/txk/util/txkISGConfigurator.xml ebsSetup -DforceStop=yes
The following prompts appear:
•Enter the password for user APPS:
•Enter the ASADMIN user name : [ASADMIN]
•Enter the password for user ASADMIN :
•The script will forcefully stop the Oracle WebLogic Server now. Do you want to proceed (yes/no)? (yes, no)
Enter yes to stop the server. The script will stop the server and then restart the server.
The above script creates and deploys the data source 'OAEADatasource' on Oracle E-Business Suite WebLogic Admin server and 'oafm_cluster1' server.

It also creates the Authentication Provider 'IsgAuthenticator' to be used by the REST services and stops the Weblogic Admin Server after accepting a confirmation
from the user. Note that apart from ISG, the data source 'OAEADatasource' is used by other Oracle E-Business Suite edge applications.
You will have to size up the data source connection pool accordingly. If the data source 'OAEADatasource' is already created,
use the -DforceDataSourceExists=true option to replace the existing data source. To proceed the setup without re-creating or overwriting the data source,
use the option -DignoreDataSourceExists=true.
use the option -DforceAuthenticationProviderExists=true to re-create the Authentication Provider.
5.Execute adop phase=fs_clone on Oracle E-Business Suite 12.2 enabled for Online Patching to copy the REST configurations done above to the other file system
6.Add directory $FND_TOP/perl to environment variable PERL5LIB
7.Download patch 13602850 and install this patch for perl library extensions. Patch archive p13602850_R12_GENERIC.zip contains following directories
a) Class-MethodMaker-1.12 b) Compress-Raw-Zlib-2.009 c) Compress-Zlib-2.009
cd [above listed directory]
and perform - perl Makefile.PL | make |make install
repeat this step to install all 3 perl modules Validating REST Services Setup Using Oracle E-Business Suite Integrated SOA Gateway Script
Execute the following script to validate the Oracle E-Business Suite Integrated SOA Gateway setup for REST services:
For example, use the following script to deploy a PL/SQL interface FND_USER_PKG as a REST service with POST method:
ant -f $JAVA_TOP/oracle/apps/fnd/isg/ant/isgDesigner.xml -Dactions=deploy -DserviceType=REST -DirepNames=FND_USER_PKG[{TESTUSERNAME:SYNC:POST}] -Dverbose=ON -Dalias=FndUserPkgSvc

Create Custom Database Objects (PL/SQL):

Now in this step, I will explain, how to register a custom PL/SQL API in Integration Repository and create a REST web services out of that.
example: the following PL/SQL package will return Application user name and E-mail address, if you pass user id as an argument.


create a package specification like this (file name xxfndus.pls), it should have irep comments required for parser.



set verify off
whenever sqlerror exit failure rollback;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
CREATE OR REPLACE PACKAGE XXFND_USER_QUERY AS
/* $Header: xxfndus.pls $ */
/*#
* This custom PL/SQL package can be used fetch user details.
* @rep:scope public
* @rep:product FND
* @rep:displayname User Information query
* @rep:category BUSINESS_ENTITY XXFND_USER_Q
*/
PROCEDURE main_proc(
p_user_id IN number
,v_name OUT VARCHAR2
,v_email OUT VARCHAR2
,v_msg OUT VARCHAR2
,v_status OUT VARCHAR2)
/*#
* Use this procedure to fetch user information
* @param P_USER_ID is the user_id of the user
* @param V_NAME Returns user name by the program
* @param V_EMAIL Returns user email by the program
* @param V_MSG Returns messages by the program
* @param V_STATUS Returns status of the Program
* @rep:displayname User Information query
* @rep:category BUSINESS_ENTITY XXFND_USER_Q
* @rep:scope public
* @rep:lifecycle active
*/
;
END XXFND_USER_QUERY;
/
commit;
exit;

Now create a package body like this (file name xxfndub.pls), no irep comments are required for package body.
CREATE OR REPLACE PACKAGE BODY XXFND_USER_QUERY AS
PROCEDURE main_proc
(p_user_id NUMBER
,v_name OUT VARCHAR2
,v_email OUT VARCHAR2
,v_msg OUT VARCHAR2
,v_status OUT VARCHAR2)
AS
BEGIN
SELECT USER_NAME , EMAIL_ADDRESS
INTO v_name,v_email
FROM FND_USER
WHERE user_id = p_user_id;
v_msg := 'Successfully completed';
v_status := 'S';
EXCEPTION
WHEN OTHERS THEN
v_msg := 'Completed with Error' ;
v_status := 'F';
END main_proc;
END XXFND_USER_QUERY;
/

Now compile package spec & body in apps
Now execute this perl command for irep parsing

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

This will generate one ildt file named xxfndus_pls.ildt
Now execute following FNDLOAD command to upload ildt file to IREP
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xxfndus_pls.ildt


  • Now connect EBS and search XXFND_USER_QUERY as internal name in Integration Repository.



  • Click the service and create REST web service for this internal PL/SQL API by clicking deploy button.
    Once REST service is deployed,view the generated WADL link
    Also please give grant to applications users for this newly registered web service in ISG.
    Now test the webservice with following payload in SOAPUI or in Chrome ARC
    web service url: http://host.domain:port/webservices/rest/xxfnd1/main_proc/

    Method: Post
    Http request header parameters:
    Content-Type: application/json
    Accept: application/json
    Authorization: Basic QVNBRE1JTjpNMGdlcm11bHVr
    Content-Language: en-US

    Payload Json:
    {
    'ISGServiceFault' : {
    'Code' : 'ISG_SERVICE_AUTH_FAILURE',
    'Message' : 'User is not authorized to execute the service',
    'Resolution' : 'Please check whether the user has the requisite grants.',
    'ServiceDetails' : {
    'ServiceName' : 'xxfnd1',
    'OperationName' : 'main_proc',
    'InstanceId' : '0'
    }}}

    out put JSON is following
    {
    'OutputParameters': {
    '@xmlns:xsi': 'http://www.w3.org/2001/XMLSchema-instance',
    '@xmlns': 'http://xmlns.oracle.com/apps/fnd/rest/xxfnd1/main_proc/',
    'V_NAME': 'SYSADMIN',
    'V_EMAIL': {...},
    'V_MSG': 'Successfully completed',
    'V_STATUS': 'S'
    }}

    Common errors in ISG REST Service Invocations are:-


    Policy requires authentication token.


    Cause: This error is thrown by server when either Web Service Security Username Token in Security header is missing or is invalid.


    Resolution: Pass a valid Web Service Security Username Token in Security header.


    The security token could not be authenticated or authorized


    Cause: This error is thrown by server when username or(/and) password in Web Service Security Username Token is invalid.


    Resolution: Pass valid oracle applications username and password in Web Service Security Username Token.


    User not authorized to execute service


    Cause: This error is thrown by server when user given in Web Service Security Username Token is not authorized to execute the Web Service function being invoked in SOAP Request. 

    Resolution: Create grant at user, role or global level from Integration Repository UI to authorize the the user to execute Web Service function. Clear cache from Functional Administrator Responsibility. 


    Fault from server : Responsibility key is not valid


    Cause: This error is thrown by server when Responsibility Key passed in Responsibility Header in Header is invalid.


    Resolution: Server expects a valid Responsibility Key in Responsibility Header in Header.


    Use this query to find valid Responsibility Keys for a particular user:-


    Select resp.RESPONSIBILITY_KEY, grp.SECURITY_GROUP_KEY,
    APP.APPLICATION_SHORT_NAME
    From FND_USER_RESP_GROUPS furg, FND_USER usr, fnd_responsibility_vl
    resp,FND_SECURITY_GROUPS grp,FND_APPLICATION APP
    where furg.user_id=usr.user_id
    and furg.RESPONSIBILITY_ID=resp.RESPONSIBILITY_ID
    and furg.SECURITY_GROUP_ID=grp.SECURITY_GROUP_ID
    and furg.RESPONSIBILITY_APPLICATION_ID=APP.APPLICATION_ID
    and usr.user_name= :username

    Responsibility Application Short name is not valid.


    Cause: This error is thrown by server when Application Short name in RespApplication Header in Header is invalid.


    Resolution: Server expects a valid Application short name in RespApplication Header in Header. Use query given above to find a valid Application short name. 


    Security Group Key is not valid.


    Cause: This error is thrown by server when Security Group Key in Security Group Header in Header is invalid.


    Resolution: Server expects a valid Security Group Key in SecurityGroup Header in Header. Use query given above to find a valid Security Group Key. 


    NLS Language is not valid.


    Cause: This error is thrown by server when NLS Language in NLS Language Header in Header is invalid.


    Resolution: Server expects a valid NLSLanguage value in NLSLanguage Header in Header. Use the following query to find a valid NLSLanguage:


    SELECT NLS_LANGUAGE FROM FND_LANGUAGES WHERE INSTALLED_FLAG in (‘B’,’I’);

    Service is not deployed.


    Cause: This error is thrown by server when invoked service is generated but not deployed.


    Resolution: Deploy this service from Integration Repository UI.


    #REST,#ISG,#Oracle,#E-business