Oracle APPS Integrated SOA Gateway Publishing Web Service

Share Button


In this tutorial, I am going to explain how to publish web services with Integrated SOA Gateway.

Integrated SOA Gateway is an external module which can be installed on Oracle EBS. We can publish web services with pl/sql in a very simple way with Integrated SOA Gateway. I am going to create a custom service which gets teams and team players from a consumer client and insert them into custom tables which are logically associated with each other.

1. Since we are going to implement a custom web service, it will better to create a new custom entity. If the requirement is related to a specific EBS module, then it would be better to use a predefined business entity. We are going to create our web service in this custom entity. To define a custom entity, open lookups form and query BUSINESS_ENTITY. Insert a new line and give a proper entity name.soa_gw_1

2. We need to create our tables which are going to hold team and player information. Run the following script and create team table and player table with their public synonyms and sequences. Our data model is 1-*. We need to create web service according to this data model.

XXNTC_ANIL_TEAMS: This table is going to keep team data.

XXNTC_ANIL_TEAM_PLAYERS: This table is going to keep players data. It is logically connected to team table via team_id.

XXNTC_ANIL_TEAM_RESULTS: This table is going to keep result of the services based on teams. We are going to insert every team record that we processed successfully or unsuccessfully.

3. In order to create a service which reflects our data model, we need to create Pl/Sql OBJECTS and TABLE TYPES. Run the following scripts one by one.

XX_PLAYER: Keeps a player data.

XX_PLAYER_TABLE: Keeps many player data. This is a table type which is constructed from XX_PLAYER object type.

XX_TEAM: This object is going to keep team data. In order to make a team has many players, we need to add xx_player_table TABLE TYPE as an attribute in this object.

XX_TEAM_TABLE: Keeps many table data. This is a table type which is constructed from xx_team object.

XX_TEAM_RETURN: We are going to use this object as a response type for our service.

XX_TEAM_RETURN_TABLE: Since in one requests, we may get more than one team, we also need to give a response for all of the teams that are sent with request.

4. After all of the OBJECTS and TABLE TYPES are created, we need to create our Pl/Sql package. SPEC file should be saved with pls extension. We are going to use some defined annotations in spec file. These annotations will provide information about service. Later, these information will be inserted into EBS SOA tables. Please do the following steps:

  • Write a header for your pls file.
  • Put a new line and define your service information. Do not put more than one line!
    • scope: public
    • product: XXNTC: This is a custom application. You can also use standard applications.
    • displayname: Service Display name
    • category: Provide a business entity. You can use pre-defined entities or custom entity which we created in first step.
  • Write annotations for every procedure or function which are going to be in your service. In this example, send_teams_data will be published in the service. Other procedures or functions are used for internal business logic.

Create your body file. We will iterate TABLE TYPE objects and insert data into our custom tables. The “replace” function is used for replacing null character which may be send by some client as NULL. In Pl/Sql chr(0) equals “\0” in C. If you do not replace, then it won’t be null and your if checks will probably fail.

5. Compile packages on APPS.

6. Connect to application server with FTP and upload pls file into /tmp folder or some custom folder.

7. Connect to application server with SSH and run the following unix command. This command runs a perl script which is going to parse your pls file according to the annotations you defined and generates an ildt file.

8. After ILDT file is created successfully, give proper permissions.

9. Use FNDLOAD program in order to upload ILDT file into Oracle EBS. This will insert service information into EBS tables.

10. Login with SYSADMIN into EBS and navigate to Integrated SOA Gateway -> Integration Repository.


11. Click to the “Search” button.


12. Write your service short name into Internal Name text input and click to “Go” button. Then click to the service link.


13. Click to the “Generate WSDL” button and wait for SOA to create WSDL for your service.

soa_gw_5 soa_gw_6

14. In order to give permissions to the users or groups, do the followings.

soa_gw_7 soa_gw_8

15. Check “Username Token” and Click to the “Deploy” button. This will add wsse:security to your service.


16. Click to the “View WSDL” link in order get WSDL Url.



1. Install SoapUI free version.

2. Create soap project in SoapUI with your service WSDL url.

3. Copy and paste the following into request.

4. Run the request.

5. Get the response.

6. Check your tables.






Delete Service: