Before You Install the Recipe

You have to complete certain configuration tasks on Oracle Autonomous Transaction Processing to successfully connect to it from Oracle Integration and resequence messages.

Complete the prerequisite tasks for creating a connection. See Prerequisites for Creating a Connection in Using the Oracle Autonomous Transaction Processing Adapter with Oracle Integration 3.

Additionally, as the recipe uses database tables to store the input messages, you must create the following database tables in Oracle Autonomous Transaction Processing.

Create table RSQ_CONFIG using the following command.

(
  TYPE         VARCHAR2(40 BYTE) NOT NULL,
  MAXCONCUR    NUMBER NOT NULL,
  TIMEWINDOW   NUMBER NOT NULL,
  CREATIONTIME TIMESTAMP(3) NOT NULL,
  UPDATETIME   TIMESTAMP(3) NOT NULL,
  CONSTRAINT RSQ_CONFIG_PK PRIMARY KEY
  (
    TYPE
  )
);

Create table RSQ_GROUP using the following command.

(
  ID           VARCHAR2(40 BYTE) NOT NULL,
  TYPE         VARCHAR2(40 BYTE) NOT NULL,
  STATUS       VARCHAR2(10 BYTE) NOT NULL,
  CREATIONTIME TIMESTAMP(3) NOT NULL,
  STATUSTIME   TIMESTAMP(3) NOT NULL,
  CONSTRAINT RSQ_GROUP_PK PRIMARY KEY
  (
    ID,
    TYPE
  )
);

Create table RSQ_MESSAGE using the following command.

(
  ID           VARCHAR2(40 BYTE) NOT NULL,
  GRP_ID       VARCHAR2(40 BYTE) NOT NULL,
  GRP_TYPE     VARCHAR2(40 BYTE) NOT NULL,
  SEQ_ID       NUMBER NOT NULL,
  STATUS       VARCHAR2(20 BYTE) NOT NULL,
  CREATIONTIME TIMESTAMP(3) NOT NULL,
  STATUSTIME   TIMESTAMP(3) NOT NULL,
  PAYLOAD  CLOB NOT NULL,
  CONSTRAINT RSQ_MESSAGE_PK PRIMARY KEY
  (
    ID,
    GRP_ID,
    GRP_TYPE
  )
);

Create or replace procedure PROC_RSQ_CONFIG:

(
    GTYPE IN VARCHAR2,
    MAXCONCUR IN NUMBER,
    TIMEWINDOW IN NUMBER
    )

as CC NUMBER

BEGIN
    SELECT COUNT(*) INTO CC FROM RSQ_CONFIG WHERE TYPE=GTYPE;
    IF (CC = 0) THEN
        INSERT INTO RSQ_CONFIG VALUES (GTYPE, MAXCONCUR, TIMEWINDOW, SYSDATE, SYSDATE);
    END IF;
END PROC_RSQ_CONFIG;

Create or replace procedure PROC_RSQ_GROUP:

(
    GRP_ID IN VARCHAR2,
    GRP_TYPE IN VARCHAR2
    )

GC NUMBER;

GS VARCHAR2(10)

BEGIN
    SELECT COUNT(*) INTO GC FROM RSQ_GROUP WHERE ID=GRP_ID AND TYPE=GRP_TYPE;
    IF (GC = 0) THEN
        INSERT INTO RSQ_GROUP VALUES (GRP_ID, GRP_TYPE, 'N', SYSDATE, SYSDATE);  
    ELSE
        SELECT STATUS INTO GS FROM RSQ_GROUP WHERE ID=GRP_ID AND TYPE=GRP_TYPE;
        IF (GS = 'C') THEN
            UPDATE RSQ_GROUP SET STATUS='N' WHERE ID=GRP_ID AND TYPE=GRP_TYPE;
        END IF;
    END IF;
END PROC_RSQ_GROUP;

Create or replace procedure PROC_RSQ_GROUP_DONE:

(
    GID IN VARCHAR2,
    GTYPE IN VARCHAR2
    )

MC NUMBER;

GS VARCHAR2(10);

BEGIN
    SELECT COUNT(*) INTO MC FROM RSQ_MESSAGE WHERE GRP_ID=GID AND GRP_TYPE=GTYPE;
    IF (MC = 0) THEN
        UPDATE RSQ_GROUP SET STATUS='C', STATUSTIME=SYSDATE WHERE ID=GID AND TYPE=GTYPE;
    ELSE
        UPDATE RSQ_GROUP SET STATUS='N', STATUSTIME=SYSDATE WHERE ID=GID AND TYPE=GTYPE;
    END IF;
END PROC_RSQ_GROUP_DONE;