Creating Oracle database connectivity in OIC - Part 3 (Integration Creation and testing)

This post is an continuation of post 1 and Post 2.  In both the post we laid down the base for the connectivity between OIC and database. Now we need to create an integration which will use this connection.

This example is quite basic example, which create an entry in the order table of inventory schema. This schema is created by me in this database. If you are  new to oracle database, then follow the steps.


DB Schema Creation.
1. Once you are connected to database in part 1 of the post, then create a user "Inventory" and provide resource to connect.

> create user inventory identified by "password";  -- use your own password.

> alter user inventory grant connect, resource;

2. Now login using this user Inventory with password and create a simple table using below command.
CREATE TABLE "inventory"."ORDER_MASTER"
( "ORDER_ID" NUMBER NOT NULL ENABLE,
"USER_ID" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"ORDER_VALUE" FLOAT(126),
"INVENTORY_ITEM_ID" NUMBER NOT NULL ENABLE,
"USER_TYPE" VARCHAR2(200 BYTE) DEFAULT 'N'
);
Once above step is complete, we have a table to store our information.


Integration Creation.

1. Connection:

1. In this example we are a using a rest based integration to post order details, which will be captured in the database table, been created above. Hence now create a rest based connection first, provide below details, test and save.

Connection Type:  Swagger Definition URL
Connection URL: https://<<ICS URL>>/flowapi/rest/CREATE_BULK_ORDER/v01/metadata/swagger
Enable two way SSL for outbound connections (Optional): No
Security Policy: Basic Authentication
Username: <your OIC User Name >
Password:   any password
Agent Group: None


2. Now lets create a new connection based on database as a service, if not clear use  screenshot below

3. One connection is created add following information in detail page and test.

Connection Properties  --- Configure Connectivity

Host : Database host ip address

Port:1521

SID ORCL

Security -- Configure Security

Security -- Policy Username Password Token

Username "inventory"

Password     <password for inventory db>

Agent Group ---  Configure Agents

Agent Group OracleDatabaseConnectivityAgent

5. Once tested and validated we will be able to see below screen like this for success.

Creation of integration


1. Lets go to integration and then we will choose to create an App driven Orchestration and fill all the details.

2. Now we will  create this integration and open the integration to edit and add the connection CREATE_BULK_ORDER. Once added configure the following properties, set method as GET,

3) Now lets create a few parameter which this request will take add this to request parameter as given below.

4. Now lets set some response parameters.

6. Once its done lets take the cursor on the line and add OracleDBAAS on the line. It will open a wizard to configure the database adapter.

7. Now first choose operation, you want to do on the database, in this demo we are  choose to insert but you can choose any method.

8. Select and choose the table on which operation need to be performed and then export this table using the button below.

9. Next screen on wizard will ask about the primary key, Provide the primary key of the table. Next screen will be summary, choose done there.

 10. Once we add this to integration, a mapping will come in between, the parameter which Rest is receiving and parameter which we are setting in DB Adapter. lets set it and right now hard code value for user type as "O".

11. Once everything is done,  toggle the integration button to make it active.

12. Use following URL to test the integration, it will ask for password provide the password of our user.
https://<OIC URL>/ic/api/integration/v1/flows/rest/INVENTORYUPDATE/1.0/inventoryOrderId/1?InventoryUserId=<your userid>&ValidRequestFlag=Y&OrderValue=92000&IventoryItemId=304&OrderQuantity=9000


13. Once above transaction is done lets check the database table if the data is there in the table or not, we can see data is inserted in the table which confirms everything is working.

14. We can now explore other database adapter options like executing plsql. Right now its adapter does not support complex pl/sql parameters, which oracle SOA Suite use to support by adapter design pattern.