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.
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.
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),
- (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:
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.
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.
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….
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/webtierEdit Config.pm under:
/u01/install/APPS/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multiTake 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
Note to self 
One thought on “Making use of Oracle Integrated SOA Gateway – Custom REST Services”