28 Introduction to Oracle DMW APIs

Oracle Health Sciences Data Management Workbench (Oracle DMW) is built on top of Oracle Life Sciences Data Hub (Oracle LSH) and shares the same database and execution engine. Be sure to read the following sections, which apply to Oracle DMW as well as Oracle LSH APIs:

For information on Oracle DMW functionality and structure, see the Oracle Health Sciences Data Management Workbench User's Guide, including information on object ownership (namespaces) in the Reference Information appendix.

Note:

Even though an Oracle DMW Study is an Oracle LSH domain, it has additional attributes and you cannot create a study using the API for creating a domain. The API for creating a study is not public in Release 2.4. Create studies in the user interface.

Note:

During its initial development, Oracle DMW was known as DME. Therefore many internal names contain the string dme. Please think of DME as a synonym for DMWâjust as CDR was the early name for Oracle LSH.

This section includes:

Section 28.1, "Set Up Study Environment"
Section 28.2, "Create or Modify an Expression"
Section 28.3, "Using APIs to Create Custom Programs"

28.1 Set Up Study Environment

The program you are writing must call DME_PUB_INITIALIZATION.SetupAPIStudyEnvironment before it calls any other Oracle DMW API or uses any public view. This procedure checks that the study ID and lifecycle value you pass in are valid and then uses those values and the study's partition ID for the lifecycle to set SYS_CONTEXT appropriately. Call it again to change the study or lifecycle context.

DME_PUB_INITIALIZATION.SetupAPIStudyEnvironment also calls the Oracle LSH security package cdr_pub_api_initialization; see Section 1.3.2, "Calling the Security API Package".

Note:

Like CDR_PUB_API_INITIALIZATION, access to DME_PUB_INITIALIZATION must be granted by an administrator to each individual user to start using the external APIs.

28.1.1 Initialize a Study and Lifecycle

Name DME_PUB_INITIALIZATION.SetupAPIStudyEnvironment

Signature

PROCEDURE SETUPAPISTUDYENVIRONMENT,
( P_API_VERSION IN NUMBER,   P_INIT_MSG_LIST IN VARCHAR2 DEFAULT CDR_PUB_DEF_CONSTANTS.G_FALSE,   P_COMMIT IN VARCHAR2 DEFAULT CDR_PUB_DEF_CONSTANTS.G_FALSE,   P_VALIDATION_LEVEL IN NUMBER DEFAULT CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,   X_RETURN_STATUS OUT NOCOPY VARCHAR2,   X_MSG_COUNT OUT NOCOPY NUMBER,   X_MSG_DATA OUT NOCOPY VARCHAR2,   PI_COMPANY_ID IN NUMBER,   PI_STUDY_ID IN NUMBER,   PI_LIFECYCLE IN VARCHAR2,
  );

Parameters This API has standard parameters (see "Standard Parameters") as well as the following parameters:

PI_COMPANY_ID (Mandatory). Enter COMPANY_ID of Study.
PI_STUDY_ID (Mandatory). Enter OBJ_ID of Study.
PI_LIFECYCLE (Mandatory). Enter Lifecycle context values like $LIFECYCLE$DEV, $LIFECYCLE$QC or $LIFECYCLE$PROD.

28.2 Create or Modify an Expression

In Oracle DMW, expressions in both validation checks and transformations are defined in the EXPR_OBJ_TYPE attribute of the DME_MAP_ENTITY_OBJ_TYPE. The EXPR_OBJ_TYPE attribute is of the DME_XFORM_EXPR_OBJ_TYPE object and its required values are:

EXPRESSION_ID: If you are modifying an expression, enter the expression object ID.
EXPRESSION_MODE: Enter one:
- $EXPRMODE$CRITERIA if it is a criteria expression.
- $EXPRMODE$EXPR if it is a column expression.
EXPRESSION_TEXT: Enter the string representation of expression, adhering to the following rules:
- All the values should be single quoted. Numeric values can be applied without single quotes.
- The column names should be specified in the following format: {ModelName.TableName.ColumnName}. For example, to specify a condition on the 'AGE' column from 'DM' table in 'Source' data model that AGE should be greater than 30, the expression_text should be passed as: {Source.DM.AGE} > 30
- To specify a user-defined functions, the package should be available in the view DME_PUB_XFM_EXPR_STATIC_PKGS_V. Refer to the static reference details for the package that contains the function.
EXPRESSION_ITEM_COLL - Use this parameter only if you are calling a function in the expression. This is a parameter of collection type DME_XFORM_EXPR_ITEM_OBJ_COLL which is a table of DME_XFORM_EXPR_ITEM_OBJ_TYPE object type.

For each expression_text, the collection must be populated with one record with the following attribute assignments:
```
PARENT_ROW_ID = -1; ROW_ID=0; ROW_POS=1; ITEM_TYPE_RC=' $EXPITEMTYPE$GROUP'
```
If user-defined functions are used in expression_text, the collection should be populated with a separate record for each distinct user-defined function used in expression. The following attributes should be assigned for a record used for the user defined function:
- PARENT_ROW_ID = 0;
- ROW_POS = 1;
  
  Note:
  The above two values should always be 0 and 1, respectively.
- ROW_ID - The row_id must be unique and sequential starting from 1 for each distinct user defined function used in the given expression.
- ITEM_TYPE_RC =' $EXPITEMTYPE$FUNCTION';
Enter values for the following two attributes from the DME_XFM_EXPR_STATIC_PKGS_V view.
- ITEM - Enter the name of the user-defined function.
- STATIC_REFS - Enter the function ID corresponding to the function used in expression.

28.3 Using APIs to Create Custom Programs

In Oracle DMW you may need to write custom programs to handle special cases in either transformations or validation checks. These programs must exist as user-defined program objects in Oracle LSH. You can create them in the Oracle LSH user interface, uploading the source code, or you can use APIs; see Chapter 10, "Programs."

For more information, see the sections on custom programs in the Oracle Health Sciences Data Management Workbench User's Guide in the chapters on transformations and validation checks. In addition, see the chapter on programs in the Oracle Life Sciences Data Hub Application Developer's Guide.