1. API Guide
  2. Oracle Health Sciences Data Management Workbench APIs
  3. Clinical Data Models
  4. Create and Modify Clinical Data Models
  5. Add a Column to a Table in a Clinical Data Model

Add a Column to a Table in a Clinical Data Model

Use this API to add a column to a table in a clinical data model.

Name

DME_PUB_DF_DATA_MODEL.AddColumnToDataModelTab

Signature

procedure AddColumnToDataModelTab( 
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
pio_naming 		IN OUT NOCOPY cdr_naming_version_obj_type
pio_variable 		IN OUT NOCOPY cdr_var_obj_type
pio_column 		IN OUT NOCOPY cdr_dm_column_obj_type );

Parameters

This API has standard parameters (see Standard Parameters) and the following parameters:

  • PIO_NAMING This is a parameter of type CDR_NAMING_VERSION_OBJ_TYPE. Enter values for the column and its namespace, the table. Six attributes are required:
    • COMPANY_ID Company ID of the Table.
    • OBJECT_TYPE_RC Enter $OBJTYPES$COLUMN.
    • NAME Name of the column.
    • NAMESPACE_OBJ_ID Object ID of the Table.
    • NAMESPACE_OBJ_VER Object Version of the Table.
    • DESCRIPTION Description of the column.
  • PIO_VARIABLE (Mandatory): This is a parameter of type CDR_VAR_OBJ_TYPE. Column objects are based on variable objects. Enter values as follows:
    • COMPANY_ID To get your company ID, use CDR_PUB_DEF_FACTORY_UTILS.GetCompanyId.
    • ORACLE_NAME: Enter an Oracle name for the column.
    • ORACLE_DATATYPE_RC: Specify the data type. Enter one:

      '$ORADATATYPES$VARCHAR2', '$ORADATATYPES$DATE', or '$ORADATATYPES$NUMBER'

    • LENGTH:

      For Date data type columns, no length is required.

      For VARCHAR2 data type columns, the value must be between 1 and 4000.

      For Number data type columns, the maximum value is 38.

    • PRECISION: If the column is of data type Number, enter the precision.
    • SAS_V6_NAME: Enter a name that conforms to SAS V6 requirements, if needed.
    • SAS_V8_NAME: Enter a name that conforms to SAS V8 requirements, if needed.
    • SAS_LABEL: Enter a SAS label, up to 200 characters.
    • SAS_FORMAT: Enter a SAS format or leave blank to default to the value you entered for LENGTH preceded by a dollar sign ($).
    • NULLABLE_FLAG_RC: If null values are allowed in the column, enter '$YESNO$YES'. If not (the column is mandatory) enter '$YESNO$NO'.
    • DEFAULT_VALUE: Enter NULL or, if the column should always have a particular data value, enter the value.
  • PIO_COLUMN: This is a parameter of type CDR_DM_COLUMN_OBJ_TYPE. Some attributes are the same as for the variable parameter PIO_VARIABLE. The following attributes are required: COMPANY_ID, OBJ_ID, OBJ_VER, POSITION, SAS_LABEL, NULLABLE_FLAG_RC. Enter the same values you did for the variable. In addition:
    • OBJ_ID and OBJ_VER: Enter NULL.
    • POSITION: Enter the column's position in the table relative to other columns.
    • REQUIRED_FLAG_RC: Enter the opposite value that you entered for Nullable in the Variable parameter.
    • NULLABLE_FLAG_RC: Enter the same value that you entered for Nullable in the Variable parameter.
    • CODE_LIST_COMPANY_ID: To associate the column with a codelist, enter the company ID of the codelist. To find this and related values, query public view DME_PUB_CODELIST_V.
    • CODE_LIST_OBJ_ID: To associate the column the column with a codelist, enter the object ID of the codelist.
    • CODE_LIST_OBJ_VER: To associate the column the column with a codelist, enter the object version of the codelist.
    • IDENTIFIER_VAR_COMP_ID: If you are assigning an SDTM ID to the column, enter the company ID of SDTM identifier. To find this and related values, query public view DME_SDTM_COL_IDENTIFIERS_V.
    • IDENTIFIER_VAR_OBJ_ID: If you are assigning an SDTM ID to the column, enter the object ID of SDTM identifier.
    • IDENTIFIER_VAR_OBJ_VER: If you are assigning an SDTM ID to the column, enter the object version of SDTM identifier.
    • USABLE_FLAG_RC: Enter '$YESNO$YES' or '$YESNO$NO'. This value is not used by the system.
    • MAPPABLE_FLAG_RC: Enter '$YESNO$YES' if the column can be mapped to a source column in a transformation. Internal columns such as CDR$DUP_NUM and CDR$SKEY should have the value '$YESNO$NO'.

      If set to '$YESNO$NO', the system ignores the column when calculating mapping completeness and when automapping.

    • MASKING_VALUE: If the column belongs to a table with column-level blinding and should be blinded, enter the masking value.
    • MASKING_CRITERIA: If the column data values should be masked only in some rows, enter the masking criteria as a SQL expression.
    • SOURCE_KEY_FLAG_RC: Enter null.
    • ALIASES: Enter one or more alternate names for the column in a comma-separated list with no spaces, for use in automapping transformations.
    • INFORM_REF_PATH_NAME: Leave blank.
    • MASKING_LEVEL_RC: Specify the masking level for the column: , '$MASKING_LEVEL$NONE', '$MASKING_LEVEL$COLUMN', or '$MASKING_LEVEL$CELL'.
    • MASK_VALUE_EXPR_ID: To use a predefined expression to generate masking values, enter the expression's object ID.
    • MASK_VALUE_EXPRESSION: If Masking Value is '$MASKING_LEVEL$NONE' then pass NULL values. For others pass expression details to parameter DME_XFORM_EXPR_OBJ_TYPE type; see Add a Table to a Clinical Data Model parameter BLINDING_CRITERIA_EXPRESSION.
    • MASK_CRITERIA_EXPR_ID: To use a predefined expression to determine which cells to mask, enter the expression's object ID.
    • MASK_CRITERIA_EXPRESSION: If Masking value is '$MASKING_LEVEL$COLUMN' or '$MASKING_LEVEL$NONE' then pass the NULL values. For '$MASKING_LEVEL$CELL' pass criteria expression details to parameter DME_XFORM_EXPR_OBJ_TYPE type; see Add a Table to a Clinical Data Model parameter BLINDING_CRITERIA_EXPRESSION.

Parent topic: Create and Modify Clinical Data Models