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;

No comments:

Post a Comment