ORCH Tables and Their Structure

Owned by Dmitry Donosiyan (Unlicensed)
Last updated: Nov 08, 2024 by Kinga Huszno
2 min read

Currently, there are three ORCH tables in the database:

ORCH_REQUEST_DEF

PACE_MASTERDBO.ORCH_REQUEST_DEF table contains definitions of orchestration requests. It has a parent table EGL_SCHED_DEF. Due to the fact that Orchestration is a data driven process, any time a request for Orchestration arrives, it has to be treated as a request to create a schedule in terms of Process Center framework.

Column Name

Column Type

Nullable

Comments

Column Name

Column Type

Nullable

Comments

INSTANCE

NUMBER(38,0)

N

A sequence number for orchestration request

SCHED_DEF_INSTANCE

NUMBER(38,0)

N

Reference to a EGL_SCHED_DEF table instance field

CORRELATION_ID

VARCHAR2(255)

N

Unique correlation identifier

BUS_TASK_ID

VARCHAR2(255)

N

Unique business identifier, user readable

PROC_SEQ_NUMBER

NUMBER(38,0)

 

Processing Sequence Number

SEQ_NUMBER

NUMBER(38,0)

 

Sequence identifier

TOT_COUNT

NUMBER(38,0)

 

Total Count

SENT_BY

VARCHAR2(255)

 

Send By

REPLY_TO

VARCHAR2(255)

 

Reply To

PROC_STATUS

VARCHAR2(10)

N

One of SUCCESS, FAILURE, NO_DATA

CF_NAME

VARCHAR2(1000)

 

Name of the unique temporary control file

UPDATE_DATE

DATE

N

Date last updated

UPDATE_SOURCE

VARCHAR2(255)

N

User ID who updated the record

ORIG_CORRELATION_ID

VARCHAR2(255)

 

Original correlation identifier

Primary key: INSTANCE
Unique index: CORRELATION_ID
Non-unique index: ORIG_CORRELATION_ID

ORCH_REQUEST_PARAMS

The PACE_MASTERDBO.ORCH_REQUEST_PARAMS contains a list of parameters for each orchestration request. Once again, the fact that it is a data driven architecture makes the list of parameters impossible to be predefined. So, most important and common parameters are captured in the ORCH_REQUEST_DEF table and all other parameters are stored in a one-to-many relationship child table as key-value pairs.

Column Name

Column Type

Nullable

Comments

Column Name

Column Type

Nullable

Comments

INSTANCE

NUMBER(38,0)

N

Sequence number for the orchestration parameter record

ORCH_INSTANCE

NUMBER(38,0)

N

Sequence number for the orchestration request. Identifies the request in ORCH_REQUEST_DEF table.

PARAMETER_NAME

VARCHAR2(255)

N

Parameter name.

PARAMETER_VALUE

VARCHAR2(4000)

 

Parameter value.

PARAMETER_BLOB_VALUE

BLOB

 

Parameter value in case of a blob.

UPDATE_DATE

DATE

N

Date last updated

UPDATE_SOURCE

VARCHAR2(255)

N

User ID who updated the record

Primary key: INSTANCE
Unique index: ORCH_INSTANCE, PARAMETER_NAME

ORCH_QUEUE

The PACE_MASTERDBO.ORCH_QUEUE table contains a list of orchestration requests. Its parent table is EGL_SCHED_QUEUE.

Column Name

Column Type

Nullable

Comments

Column Name

Column Type

Nullable

Comments

INSTANCE

NUMBER(38,0)

N

Sequence number for orchestration request

SCHED_QUEUE_INSTANCE

NUMBER(38,0)

N

Reference to a EGL_SCHED_QUEUE table instance field

ORCH_REQ_DEF_INSTANCE

NUMBER(38,0)

N

Reference to a ORCH_REQUEST_DEF table instance field

ORCH_STATE

BLOB

 

State of orchestration workflow

STATUS

NUMBER(38,0)

 

Current detailed status of workflow

CREATE_DATE

DATE

N

Date created

UPDATE_SOURCE

VARCHAR2(255)

N

User ID who updated the record

UPDATE_DATE

DATE

N

Date last updated

ORCH_STATE_CLOB

CLOB

 

State of orchestration workflow in case of a clob

CORRELATION_ID

VARCHAR2(255)

 

Unique correlation identifier

PROCESS_CORRELATION_ID

VARCHAR2(255)

 

Process correlation identifier

ORIG_CORRELATION_ID

VARCHAR2(255)

 

Original correlation identifier

Primary key: INSTANCE
Unique index: SCHED_QUEUE_INSTANCE
Unique index: ORCH_REQ_DEF_INSTANCE
Non-unique index: CORRELATION_ID
Non-unique index: PROCESS_CORRELATION_ID
Non-unique index: ORIG_CORRELATION_ID

Joins between ORCH tables: