Sunday, 23 September 2018

CRUD Operation in Oracle Integration Cloud

In the five series of blog, we are gonna to show the CRUD Operation in Oracle Integration Cloud ServiceCRUD stands for Create, Read, Update and Delete.

We are going to create REST services with the following HTTP method:

HTTP Method
Operation
Description
POST
Create
Create Users
GET
Read All
Retrieve All Users
GET
Read
Retrieve a User By Id
PUT
Update
Update a User By Id
DELETE
Delete
Delete a User By Id

Below are the pre-requisite to perform the use cases
  • Oracle Integration Cloud (OIC) with version 18.3.3
  • Oracle Database
In the series of blog, we'll implement the REST services which will show the CRUD operation on the Oracle database table (USER_MASTER)

Below are the SQL script

Create Table Script:

CREATE TABLE "USER_MASTER"
("ID" NUMBER(20,0) NOT NULL ENABLE, “FIRST_NAME" VARCHAR2(200 BYTE), "LAST_NAME" VARCHAR2(200 BYTE), "DESIGNATION" VARCHAR2(200 BYTE), "QUALIFICATION" VARCHAR2(200 BYTE),               CONSTRAINT "USER_MASTER_PK" PRIMARY KEY ("ID") );

Sequence Script:

CREATE SEQUENCE "USERS_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE ;

Trigger Script:

CREATE OR REPLACE TRIGGER USERS_TRG
BEFORE INSERT ON USERS
FOR EACH ROW
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.ID IS NULL THEN
      SELECT USERS_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

CRUD Operation in Oracle Integration Cloud (Part-5)

This is the fifth part of DB CRUD operation in Oracle Integration Cloud(OIC).

Use Case: Create a REST service which will delete a user record based on Id

Follow the article for the pre-requisite.

Let's get started and implement the use case
  • Login in the Oracle Integration Cloud and navigate to the Integration tab
  • Click on Create button and select App Driven Orchestration template

  •  Enter the Integration Name as DB_Delete and click on Create button


  • Select Rest_Trigger_conn

  • Enter below information and click Next button
    • What do you want to call your endpoint: UserDelete
    • What is the endpoint's relative resource URI?: /users
    • What action do you want to perform on the endpoint: DELETE
    • Add and review parameters for this endpoint: Select this checkbox
    • Configure this endpoint to receive the response: Select this checkbox

  • Add Query Parameters as UserId of type integer and click Next button


  • Select JSON Sample from the drop down and then click on <<<inline>>> link and enter below json

{
    "status": ""
}

  • Just complete the wizard
  • Select Test_DB_Connection
  • Enter below details and click Next button
    • What do you want to call your endpoint: DeleteUser
    • What operation do you want to perform?: Run a SQL statement

  • Enter below SQL query and then Click Validate SQL Query

DELETE FROM USER_MASTER WHERE ID=#USER_ID


  • Edit the DeleteUser mapper


  • Map UserId => USER_ID 


  • Drop a mapper just below the DB adapter and hardcode the value of status as "User Deleted Successfully"

Now the configuration is completed. Just save the integration and Activate.

This is the time to test the REST service. POSTMAN client will be used the test the service. Enter the REST service endpoint in the POSTMAN and add BASIC authentication.


Open the Database table and verify the record with id 2 should be deleted


CRUD Operation in Oracle Integration Cloud (Part-4)

This is the fourth part of DB CRUD operation in Oracle Integration Cloud(OIC).

Use Case: Create a REST service which will update a user record based on Id

Follow the article for the pre-requisite.

Let's get started and implement the use case
  • Login in the Oracle Integration Cloud and navigate to the Integration tab
  • Click on Create button and select App Driven Orchestration template
  • Enter the Integration Name as DB_Update and click on Create button
  • Select Rest_Trigger_conn
  •  Enter below information and click Next button
    • What do you want to call your endpoint: UpdateUser
    • What is the endpoint's relative resource URI?: /users
    • What action do you want to perform on the endpoint: PUT
    • Add and review parameters for this endpoint: Select this checkbox
    • Configure a request payload for this endpoint: Select this checkbox
    • Configure this endpoint to receive the response: Select this checkbox
  • Add Query Parameters as UserId of type integer and click Next button
  • Select JSON Sample from the drop down and then click on <<<inline>>> link
{
   "User":{
      "FIRSTNAME":"",
      "LASTNAME":"",
      "DESG":"",
      "QUAL":""
   }
}

  • Select JSON Sample from the drop down and then click on <<<inline>>> link and enter below json
{
    "status": ""
}

  • Just complete the wizard
  • Select Test_DB_Connection
  • Enter below details and click Next button
    • What do you want to call your endpoint: UpdateUsers
    • What operation do you want to perform?: Perform an Operation On a Table
    • Select the Update radio button
  •  Select below information and click Import Tables button
    • Schema: HR
    • Table Type: TABLE
    • Table Name: Enter %USER% and click the Search button
    • Select USER_MASTER table from Available pane and move to the Selected pane
  • Click Next button 
  • Edit the UpdateUser mapper and map the fields 

  •  Drag a mapper just below the DB adapter and hardcode the value of status as "User Updated Successfully"
Now the configuration is completed. Just save the integration and Activate.

This is the time to test the REST service. POSTMAN client will be used the test the service. Enter the REST service endpoint in the POSTMAN and add BASIC authentication.

Add below JSON payload and hit Send button

{
   "User":{
      "FIRSTNAME":"Ankur_updated",
      "LASTNAME":"Jain_Updated",
      "DESG":"TL",
      "QUAL":"MCA"
   }
}


Open the DB table and confirm if the record has been updated


Saturday, 22 September 2018

CRUD Operation in Oracle Integration Cloud (Part-3)

This is the third part of DB CRUD operation in Oracle Integration Cloud(OIC).

Use Case: Create a REST service which will select a user record based on User id

Follow the article for the pre-requisite.

Let's get started and implement the use case
  • Login in the Oracle Integration Cloud and navigate to the Integration tab
  • Click on Create button and select App Driven Orchestration template
  • Enter the Integration Name as DB_Get_By_Id and click on Create button
  • Select Rest_Trigger_conn
  • Enter below information and click Next button
    • What do you want to call your endpoint: GetUserById
    • What is the endpoint's relative resource URI?: /users
    • What action do you want to perform on the endpoint: GET
    • Add and review parameters for this endpoint: Select this checkbox
    • Configure this endpoint to receive the response: Select this checkbox
  • Add Query Parameters as UserId of type integer and click Next button



  • Select JSON Sample from the drop down and then click on <<<inline>>> link


    • Enter below json sample and click OK button
    {
        "User": {
            "id": 3,
            "FIRSTNAME": "",
            "LASTNAME": "",
            "DESG": "",
            "QUAL": ""
        }
    }

    • Just complete the wizard
    • Select Test_DB_Connection

    • Enter below details and click Next button
      • What do you want to call your endpoint: GetUserData
      • What operation do you want to perform?: Perform an Operation On a Table
      • Select the Select radio button
    •  Select below information and click Import Tables button
      • Schema: HR
      • Table Type: TABLE
      • Table Name: Enter %USER% and click the Search button
      • Select USER_MASTER table from Available pane and move to the Selected pane
    • Click on Edit link from Review and edit SQL Query section
    • Click on Edit using Expression Builder link
    • Add Query Criteria by clicking on Add New link
    • Select the information as below and click OK button:
      • First Argument: select id
      • Operator: Equal
      • Second Argument: Select Parameter radio and enter U_Id in the input box
    • Click OK
    • Click OK
    • Click Next
    • Edit the GetUser mapper
    • Map UserId -> U_Id
    • Add mapper just below the DB adapter
    • Map all the parameter from left to right
    • Configuration is completed. Just save the integration and Activate
    This is the time to test the REST service. POSTMAN client will be used the test the service. Enter the REST service endpoint in the POSTMAN and add BASIC authentication.