Making use of Oracle Integrated SOA Gateway – Custom REST Services

Following on from my last blog post ‘Using Oracle Integrated SOA Gateway – a worked example‘ I got to thinking about how easy, or not, it would be to create my own custom REST services.

I’ve worked for many customers that have many custom functions and procedures that could easily be exposed as REST web services. I started to look into it and here are some of my notes, by way of a guide, on what I did to get my first custom REST service running on the Integrated SOA Gateway of an instance of Oracle E-Business Suite 12.2.8.

Hopefully this will inspire you to have a go.

Environment used

Oracle E-Business Suite Vision Install 12.2.8 – with ISG. Refer to the following blog: EBS 12.2.8 VM Virtual Appliance Now Available

Follow the guide to help you get the Vision instance running.

Download and install Postman.

Assumptions

Your E-Business Suite Application or VM has already been patched to run the Integrated SOA Gateway (refer to Oracle Support Document 1311068.1).

Create new applications user if required

We are jumping ahead now, but thinking about once you’ve installed your custom REST service you will need to assign it to an E-Business Suite (applications) user, or group of users.

At this point you may also wish just to run a selection of generic services against a single and dedicated custom user instead. In this case (and for the purposes of this worked example),

  1. (Optional Step) If you want to use a generic applications login to be granted access to the REST services exposed on the Integrated SOA Gateway then create a new EBS user, for example:
    • Username: XXSOA_REST_UTIL
    • Description: SOA Utilities User
    • Responsibility: [Pick the correct responsibility]

Save, login for first time, change password (so you don’t have authorization/authentication issues later).


A reason why you may want a generic user to be created is to ease the Grant process (see later). In my opinion, you should only use a generic user account if the API you are executing is not creating, updating or deleting a record relating to a person.

Annotate Your Custom Package Specification

2. Annotate the package specification only. Our example package is called XXDHL_MESSAGE_STATUS_REST.pls

WHENEVER SQLERROR EXIT FAILURE ROLLBACK 
WHENEVER OSERROR  EXIT FAILURE ROLLBACK SET VERIFY OFF 
CREATE OR REPLACE PACKAGE xxdhl_message_status_rest 
--============================================================================ 
--============================================================================ 
--== 
--== Program Name: XXDHL_MESSAGE_STATUS_REST.pls 
--== Version:      1.0 
--== System Name:  CEMLI 001 
--== Sub-System:   Integration Custom Extensions 
--== Description:  Integration Message Management 
--== 
--============================================================================ 
--== 
--== Version  Date         Author               Description 
--== -------  -----------  -------------------  ------------------------------ 
--== 1.0      21-JUN-2019  I. Scorrer           Initial version 
--============================================================================ 
--== 
--== Additional Information 
--== ------------------------------------------------------------------------- 
--== This SQL*Plus script is used to create database storage objects for 
--== Integration Message Management. 
--============================================================================ 
-- ///Header Cutoff Line Do Not Remove/// 
AS 
/* $Header: XXDHL_MESSAGE_STATUS_REST.pls  21/07/2019 I.Scorrer   $ */ 
/*# 
* This custom PL/SQL package can be used to insert records into xxdhl_message_status 
* @rep:scope public 
* @rep:product FND 
* @rep:displayname MESSAGE_STATUS 
* @rep:category BUSINESS_ENTITY XXDHL_MESSAGE_STATUS 
*/    
PROCEDURE message_status 
(      p_unique_event_id          IN       VARCHAR2
     , p_xxdhl_interface_sequence IN       VARCHAR2 DEFAULT NULL
     , p_to_hub_date_time         IN       DATE DEFAULT NULL
     , p_from_hub_date_time       IN       DATE DEFAULT NULL
     , p_message_stage            IN       VARCHAR2 DEFAULT NULL
     , p_message_status           IN       VARCHAR2 DEFAULT NULL
     , p_message_information      IN       VARCHAR2 DEFAULT NULL
     , p_exception_notif_subject  IN       VARCHAR2 DEFAULT NULL
     , p_exception_cause          IN       VARCHAR2 DEFAULT NULL
     , p_error_code               IN       VARCHAR2 DEFAULT NULL
     , p_error_message            IN       VARCHAR2 DEFAULT NULL
     , p_priority                 IN       NUMBER DEFAULT NULL
     , p_file_name                IN       VARCHAR2 DEFAULT NULL
     , p_file_directory           IN       VARCHAR2 DEFAULT NULL 
    , p_event_name               IN       VARCHAR2 DEFAULT NULL
     , p_event_version            IN       VARCHAR2 DEFAULT NULL
     , p_business_object          IN       VARCHAR2 DEFAULT NULL
     , p_message_version          IN       VARCHAR2 DEFAULT NULL
     , p_soa_process_id           IN       VARCHAR2 DEFAULT NULL
     , p_external_correlation_id  IN       VARCHAR2 DEFAULT NULL
     , p_sending_application      IN       VARCHAR2 DEFAULT NULL
     , p_sender_name              IN       VARCHAR2 DEFAULT NULL
     , p_target_application       IN       VARCHAR2 DEFAULT NULL
     , p_target_name              IN       VARCHAR2 DEFAULT NULL
     , p_record_count             IN       NUMBER DEFAULT NULL
     , p_exception_handler_status IN       VARCHAR2 DEFAULT NULL
     , p_creation_date            IN       DATE DEFAULT NULL
     , p_created_by               IN       NUMBER DEFAULT NULL
     , p_last_update_date         IN       DATE DEFAULT NULL
     , p_last_updated_by          IN       NUMBER DEFAULT NULL
     , p_last_update_login        IN       NUMBER DEFAULT NULL
     , p_tsd_3_classification     IN       VARCHAR2 DEFAULT NULL
     , p_sub_category             IN       VARCHAR2 DEFAULT NULL
     , p_default_resolver_group   IN       VARCHAR2 DEFAULT NULL
     , p_work_notes               IN       VARCHAR2 DEFAULT NULL
     , x_return_status            OUT      VARCHAR2
     , x_msg_data                 OUT      VARCHAR2
    ) 
/*# 
* Insert records into xxdhl_message_status table 
* @param p_unique_event_id Indicates the unique_event_id 
* @param p_xxdhl_interface_sequence Indicates the xxdhl_interface_sequence           
* @param p_to_hub_date_time         Indicates the to_hub_date_time                   
* @param p_from_hub_date_time       Indicates the from_hub_date_time                 
* @param p_message_stage            Indicates the message_stage                      
* @param p_message_status           Indicates the message_status                     
* @param p_message_information      Indicates the message_information                
* @param p_exception_notif_subject  Indicates the exception_notif_subject            
* @param p_exception_cause          Indicates the exception_cause                    
* @param p_error_code               Indicates the error_code                         
* @param p_error_message            Indicates the error_message                      
* @param p_priority                 Indicates the priority                           
* @param p_file_name                Indicates the file_name                          
* @param p_file_directory           Indicates the file_directory                     
* @param p_event_name               Indicates the event_name                         
* @param p_event_version            Indicates the event_version                      
* @param p_business_object          Indicates the business_object                    
* @param p_message_version          Indicates the message_version                    
* @param p_soa_process_id           Indicates the soa_process_id                     
* @param p_external_correlation_id  Indicates the external_correlation_id            
* @param p_sending_application      Indicates the sending_application                
* @param p_sender_name              Indicates the sender_name                        
* @param p_target_application       Indicates the target_application                 
* @param p_target_name              Indicates the target_name                        
* @param p_record_count             Indicates the record_count                       
* @param p_exception_handler_status Indicates the exception_handler_status           
* @param p_creation_date            Indicates the creation_date                      
* @param p_created_by               Indicates the created_by                         
* @param p_last_update_date         Indicates the last_update_date                   
* @param p_last_updated_by          Indicates the last_updated_by                    
* @param p_last_update_login        Indicates the last_update_login                  
* @param p_tsd_3_classification     Indicates the tsd_3_classification               
* @param p_sub_category             Indicates the sub_category                       
* @param p_default_resolver_group   Indicates the default_resolver_group             
* @param p_work_notes               Indicates the work_notes                         
* @param x_return_status            Indicates the return_status                      
* @param x_msg_data                 Indicates the msg_data    
* @rep:displayname                  MESSAGE_STATUS 
* @rep:category                     BUSINESS_ENTITY XXDHL_MESSAGE_STATUS                       
* @rep:scope public 
* @rep:lifecycle active 
*/ ; 
END xxdhl_message_status_rest; 
/ 

Install The Custom Package Specification (And Body)

3. Install the package specification (and body) to the database in exactly the same way as you usually do (either directly in sql plus, sql developer, or via your favorite UNIX CEMLI installer).

Run The Integration Repository Parser

4. The Integration Repository (iREP) Parser does not support the integration interfaces registered under custom applications. We will therefore need to register this service under the FND TOP. Copy the package specification (e.g. XXDHL_MESSAGE_STATUS_REST.pls) to the $FND_TOP/patch/115/sql directory.

5. Create the iLDT file by running the following:

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin FND:patch/115/sql:XXDHL_MESSAGE_STATUS_REST.pls:1.0=XXDHL_MESSAGE_STATUS_REST.pls

You should see a message like this:

# Interface Repository Annotation Processor, 12.0.0
#
# Generating annotation output.
# Processing file 'XXDHL_MESSAGE_STATUS_REST.pls'.
# Using YAPP-based parser.
#  Found a package-level annotation for 'XXDHL_MESSAGE_STATUS_REST'.
#  Found a detail-level annotation...
# Found a procedure named 'MESSAGE_STATUS'.
# Done all files.

Note: The package specification suffix needs to be *.pls – any other suffix, for example *.pks, you will get this error message:

# Warning: An IR parser for files like 'XXDHL_MESSAGE_STATUS_REST.pks'  is not currently supported.
# Warning: Not generating iLDT for 'XXDHL_MESSAGE_STATUS_REST.pks', file to be ignored.

The iLDT file will be created in the same directory as where you run the ‘irep_parser’ command.

For those developers that have used FNDLOAD scripts in the past, take a look at the *.ildt file that gets created…it will be very familiar to a FNDLOAD script when you look inside.

Run FNDLOAD to Upload To The Integration Repository

6. Run FNDLOAD (wfirep.lct) against the new XXDHL_MESSAGE_STATUS_REST_pls.ildt file in order to create it in the Oracle Integrated SOA Gateway.

$FND_TOP/bin/FNDLOAD apps/[apps_password] 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XXDHL_MESSAGE_STATUS_REST_pls.ildt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Review Your Custom Package Against ISG

Take a look at the attached XXDHL_MESSAGE_STATUS_REST.pls package specification and examine the mark-up.

7. Using the ASADMIN or other user that has the ‘Integrated SOA Gateway’ responsibility, search for the new custom service in the Integration Repositoty. e.g. MESSAGE_STATUS

Notice how the mark-up in the *.pls file and version number (1.0) used in the irep_parser call above ends up as part of the service configuration.

Deploy The Custom REST Service

8. Click on the ‘REST Web Service’ tab. Enter the Service Alias.

The Service Alias will form part of the URL for the REST service, so it must not be too long or contain any spaces or illegal characters. Just for demonstration purposes, and as this service belongs to the xxdhl_message_status_rest package, we will use that name.

9. Check the service you wish to deploy, then press the Deploy button.

Once the REST service is deployed you will see a message like this:

Review The WADL File

With the custom API now deployed you will be able to view the WADL service. e.g. http://apps.example.com:8000/webservices/rest/xxdhl_message_status_rest?WADL

View the relationships between the WADL syntax and the service as viewed from within the Integration Repository.

Create Grants

10. Click on the Grants tab, and select the EBS applications user that is allowed to run this service. In this example we will find and select the user (XXSOA_REST_UTIL) we created as part of Step 1.

11. Click the ‘Grant’ button to save the Grant.

Test The Service In Postman

The URL is made up of the resource base and the resource path as displayed in the WADL file.

The URL for the Message Status REST service is therefore:

http://apps.example.com:8000/webservices/rest/xxdhl_message_status_rest/message_status/

12. Create a Postman test request.

Enter the Message Service URL, and under the Authorization enter the username and password for a user that has been granted access to execute it. (e.g. XXSOA_REST_UTIL).


Important Note


In Oracle eBusiness Suite 12.2.8 custom ISG services only support the POST REST method. Other methods like GET, PUT, PATCH and DELETE are not supported – however that does not mean that your custom API cannot “get” records, “update” records or “delete” records if that is its purpose….you just can’t use those verb names as REST methods.


Under the input parameters section, choose Body, raw and select the type of payload. In this example we will select the payload as a ‘JSON (application/json)’ type. Add your json to the input area.

{
    "InputParameters": {
        "P_UNIQUE_EVENT_ID": "3",
        "P_XXDHL_INTERFACE_SEQUENCE": "10004",
        "P_MESSAGE_STAGE": "REST Service called",
        "P_MESSAGE_STATUS": "Normal",
        "P_MESSAGE_INFORMATION": "Inbound REST Service executed, no mapping issues reported.",
        "P_SENDING_APPLICATION": "FACES",
        "P_SENDER_NAME": "XXSOA_REST_UTIL",
        "P_TARGET_APPLICATION": "EBSDB",
        "P_TARGET_NAME": "MESSAGE_STATUS",
        "P_RECORD_COUNT": 1
    }
}

13. When you’re ready, press Send.

If the credentials, payload and URL are correct, then you should get a Status 200 OK message, together with any output parameters returned from the API.

That’s it! Hope you enjoyed this blog post and maybe learnt something useful. Let me know.


As an extra, here’s some other stuff I noted down….

Useful Notes

Issues and Fixes – IREP_PARSER ‘compilation aborted/failed’ error

You may get the following error the first time you run the irep_parser…

BEGIN failed--compilation aborted at /u01/install/APPS/fs1/EBSapps/appl/fnd/12.0.0/perl/FND/irep/repo/Rep/Parser.pm line 24.
Compilation failed in require at /u01/install/APPS/fs1/EBSapps/appl/fnd/12.0.0/bin/irep_parser.pl line 37.
BEGIN failed--compilation aborted at /u01/install/APPS/fs1/EBSapps/appl/fnd/12.0.0/bin/irep_parser.pl line 37.

Refer to Oracle Support Doc ID 2007651.1 – where it states that the reason for the error is that ‘Use for Integration Repository Parser (irep_parser.pl) was not completed.’ It refers you to the ISG Implementation Guide section for setting up the iRep Parser.

Notes on the fix…

On the 12.2.8 VM, the location of:

$FMW_HOME/webtier = /u01/install/APPS/fs1/FMW_Home/webtier

Edit Config.pm under:

/u01/install/APPS/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi

Take a backup copy of Config.pm and edit the file under the ‘tie’ configuration section as follows:


archlibexp =>relocate_inc(‘/u01/install/APPS/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi’),

privlibexp =>relocate_inc(‘/u01/install/APPS/fs1/FMW_Home/webtier/perl/lib/5.10.0’),

sitearchexp =>relocate_inc(‘/u01/install/APPS/fs1/FMW_Home/webtier/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi’),

sitelibexp =>relocate_inc(‘/u01/install/APPS/fs1/FMW_Home/webtier/perl/lib/site_perl/5.10.0’),


Create a directory ‘perl‘ in $APPL_TOP_NE where the new Perl modules will be installed. For example,

mkdir $APPL_TOP_NE/perl
chmod 755 $APPL_TOP_NE/perl

In the run file system, set the following environment variables in APPL_TOP environment (the commands in bold are the ones that I used):

a. Prepend PATH with the path to the C compiler installed as a requirement of the Integration Repository Parser.

[I found the cc compiler by using $ which cc (/usr/bin/cc)]

export PATH=/usr/bin/cc:$PATH

b. Prepend PERL5LIB with $FND_TOP/perl and $APPL_TOP_NE/perl in that order.

For example, export PERL5LIB=$FND_TOP/perl:$APPL_TOP_NE/perl:$PERL5LIB.

export PERL5LIB=$FND_TOP/perl:$APPL_TOP_NE/perl:$PERL5LIB

c. Add $FMW_HOME/webtier/lib to LIBPATH if it is not present.

For example, export LIBPATH=$LIBPATH:$FMW_HOME/webtier/lib.

export LIBPATH=$LIBPATH:$FMW_HOME/webtier/lib

d. Set $FMW_HOME/webtier as ORACLE_HOME.

For example, export ORACLE_HOME=$FMW_HOME/webtier.

export ORACLE_HOME=$FMW_HOME/webtier

e. Prepend LD_LIBRARY_PATH with $ORACLE_HOME/lib32 and $ORACLE_HOME/lib.

For example, export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

f. Set JAVA_HOME to the JDK top directory.

Obtain the path returned by ‘which java’ and set JAVA_HOME to the current JDK top directory.

For example, on the Oracle Solaris:

which java

          /prod/EBS122/fs1/FMW_Home/jdk/jre/bin/java

export JAVA_HOME=/prod/EBS122/fs1/FMW_Home/jdk

which java

      /u01/install/APPS/fs1/EBSapps/comn/util/jdk32/jre/bin/java

export JAVA_HOME=/u01/install/APPS/fs1/EBSapps/comn/util/jdk32

Download and unzip patch 13602850 (p13602850_R12_GENERIC.zip) into a temporary area.

Patch 13602850 contains the following Perl modules:

  • Compress-Raw-Zlib-2.009
  • Compress-Zlib-2.009
  • Class-MethodMaker-1.12

Using WinSCP, copy (I used binary mode) the 3 folders to /tmp

Change directory to the perl engine, and run the copy, makefile and install process 3 times for each of the modules:

cd $APPL_TOP_NE/perl

cp -r /tmp/Compress-Raw-Zlib-2.009 .

cd Compress-Raw-Zlib-2.009

perl Makefile.PL

make

make install

cd $APPL_TOP_NE/perl

cp -r /tmp/Compress-Zlib-2.009 .

cd Compress-Zlib-2.009

perl Makefile.PL

make

make install

cd $APPL_TOP_NE/perl

cp -r /tmp/Class-MethodMaker-1.12 .

cd Class-MethodMaker-1.12

perl Makefile.PL

make install

One thought on “Making use of Oracle Integrated SOA Gateway – Custom REST Services

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s