Customize Table for CLOB columns

6 Customize Table for CLOB columns

To reduce the time taken to load the CLOB data from Oracle Argus Safety source tables to Oracle Argus Mart stage tables, add or modify the data in the table ETL_SPLIT_CLOB_COLUMN_MAP available in Oracle Argus Mart schema.

With this customization, the ETL fetches only the first 32767 bytes of the source CLOB column to the respective column of the stage table. The value in the source CLOB column splits into twenty five VARCHAR2 columns at the source before moving the data to the respective stage column. Hence, reduced loading time of these tables, and reduced ETL time.

Note:

Enabling this feature fetches only the first 32767 bytes of the data from the source CLOB column to the respective stage table column. The rest of the data is not copied.
Enable this feature purely based on your own discretion.

The following are the table details to enable or disable this customization:

COLUMN NAME	DATA TYPE (LENGTH)	DESCRIPTION
TABLE_NAME	VARCHAR2 (30)	Defines Oracle Argus Safety source table name from which data is populated in to Oracle Argus Mart stage table. For example, DLP_CASE_NARRATIVE.
COLUMN_NAME	VARCHAR2 (30)	Defines the name of the CLOB column present in the source table. For example, ABBREV_NARRATIVE.
ENABLE_YN	VARCHAR2 (1)	Defines the values for this column. Y—The feature is enabled N—The feature is disabled

COLUMN NAME

DATA TYPE (LENGTH)

DESCRIPTION

TABLE_NAME

VARCHAR2 (30)

Defines Oracle Argus Safety source table name from which data is populated in to Oracle Argus Mart stage table. For example, DLP_CASE_NARRATIVE.

COLUMN_NAME

VARCHAR2 (30)

Defines the name of the CLOB column present in the source table. For example, ABBREV_NARRATIVE.

ENABLE_YN

VARCHAR2 (1)

Defines the values for this column.

Y—The feature is enabled

N—The feature is disabled

Besides, as part of the Factory Data, the following columns of the table ETL_SPLIT_CLOB_COLUMN_MAP are disabled by default (ENABLE_YN = N):

TABLE_NAME	COLUMN_NAME	ENABLE_YN
CMN_REG_REPORTS	NULLIFICATION_REASON	N
DLP_CASE_ASSESS	BFARM_MANUAL_TEXT	N
DLP_CASE_ASSESS	EVALUATION	N
DLP_CASE_ASSESS	EVALUATION_J	N
DLP_CASE_DOSE_REGIMENS	DOSE_DESCRIPTION	N
DLP_CASE_DOSE_REGIMENS	DOSE_DESCRIPTION_J	N
DLP_CASE_EU_DEVICE	CORRECTIVE_ACTION	N
DLP_CASE_EU_DEVICE	CORRECTIVE_ACTION_FINAL	N
DLP_CASE_EU_DEVICE	COUNTRIES_OF_DISTRIBUTION	N
DLP_CASE_EU_DEVICE	CURRENT_DEV_LOCATIONS	N
DLP_CASE_EU_DEVICE	FURTHER_INVESTIGATION	N
DLP_CASE_EU_DEVICE	INDENTIF	N
DLP_CASE_EU_DEVICE	INVESTIGATION_RESULT	N
DLP_CASE_EU_DEVICE	MANUFACTURER_COMMENTS	N
DLP_CASE_EU_DEVICE	PROJECTED_TIMING	N
DLP_CASE_EU_DEVICE	PROJECTED_TIMING_FINAL	N
DLP_CASE_EVENT	DETAILS	N
DLP_CASE_EVENT	DETAILS_J	N
DLP_CASE_FOLLOWUP	JUSTIFICATION	N
DLP_CASE_FOLLOWUP	JUSTIFICATION_J	N
DLP_CASE_JUSTIFICATIONS	J_TEXT	N
DLP_CASE_JUSTIFICATIONS	J_TEXT_J	N
DLP_CASE_LAB_DATA	COMMENTS	N
DLP_CASE_LAB_DATA	COMMENTS_J	N
DLP_CASE_LAB_DATA	NOTES	N
DLP_CASE_LAB_DATA	NOTES_J	N
DLP_CASE_NARRATIVE	ABBREV_NARRATIVE	N
DLP_CASE_NARRATIVE	ABBREV_NARRATIVE_J	N
DLP_CASE_NOTES_ATTACH	NOTES	N
DLP_CASE_NOTES_ATTACH	NOTES_J	N
DLP_CASE_PAT_HIST	NOTE	N
DLP_CASE_PAT_HIST	NOTE_J	N
DLP_CASE_PAT_INFO	NOTES	N
DLP_CASE_PAT_INFO	NOTES_J	N
DLP_CASE_PROD_DEVICES	NARRATIVE_TEXT	N
DLP_CASE_PROD_DEVICES	PRELIMINARY_COMMENTS	N
DLP_CASE_PROD_DEVICES	PRELIMINARY_COMMENTS_J	N
DLP_CASE_PRODUCT	COMMENTS_TIMEFRAME	N
DLP_CASE_PRODUCT	GENERIC_NAME	N
DLP_CASE_PRODUCT	GENERIC_NAME_J	N
DLP_CASE_PRODUCT	NOTES	N
DLP_CASE_PRODUCT	NOTES_J	N
DLP_CASE_PRODUCT	QC_ANALYSIS_CAT_TEXT	N
DLP_CASE_PRODUCT	QC_ANALYSIS_CAT_TEXT_J	N
DLP_CASE_PRODUCT	QC_ANAL_SUMMARY_TEXT	N
DLP_CASE_PRODUCT	QC_ANAL_SUMMARY_TEXT_J	N
DLP_CASE_PRODUCT	QC_COMMENT	N
DLP_CASE_PRODUCT	QC_COMMENT_J	N
DLP_CASE_PRODUCT	QC_COMPLAINT_CAT_TEXT	N
DLP_CASE_PRODUCT	QC_COMPLAINT_CAT_TEXT_J	N
DLP_CASE_PRODUCT	QC_RESULT	N
DLP_CASE_PRODUCT	QC_RESULT_J	N
DLP_CASE_PRODUCT	REASON_DOWNGRADE	N
DLP_CASE_PRODUCT	RETRO_INFECTION	N
DLP_CASE_REPORTERS	NOTES	N
DLP_CASE_REPORTERS	NOTES_J	N
DLP_CASE_ROUTING	COMMENT_TXT	N
DLP_CASE_ROUTING	COMMENT_TXT_J	N
DLP_CASE_STUDY	STUDY_DESC	N
DLP_CASE_STUDY	STUDY_DESC_J	N
LM_LABELED_TERMS	NOTES	N
LM_LABELED_TERMS	NOTES_J	N
LM_PRODUCT	PROD_GENERIC_NAME	N
LM_PRODUCT	PROD_GENERIC_NAME_J	N
RPT_ROUTING	COMMENT_TXT	N

To enable this feature:

For the columns available in the table ETL_SPLIT_CLOB_COLUMN_MAP as part of the factory data, update the column value for ENABLYE_YN as Y for the required column. For example:
```
UPDATE ETL_SPLIT_CLOB_COLUMN_MAP
   SET ENABLYE_YN = 'Y'
WHERE TABLE_NAME = '<TABLE_NAME>'
   AND COLUMN_NAME = '<COLUMN_NAME>'
```
For new columns that are not available in the table ETL_SPLIT_CLOB_COLUMN_MAP as part of the factory data, create new entry in this table with the column value for ENABLYE_YN as Y for the required column. For example:
```
INSERT INTO ETL_SPLIT_CLOB_COLUMN_MAP
       (TABLE_NAME , COLUMN_NAME , ENABLYE_YN)
VALUES ('<TABLE_NAME>, '<COLUMN_NAME>', 'Y');
COMMIT;
```

Note:

Enabling this feature only affects the data that is fetched from Oracle Argus Safety Source to Oracle Argus Mart in the Next ETL. It does not affects the existing data in the MART. (It does not triggers the re-load of data for the respective CLOB columns.)

To disable this feature:

Set the column value for ENABLYE_YN as N for the required column.

For the example:
UPDATE ETL_SPLIT_CLOB_COLUMN_MAP
   SET ENABLYE_YN = 'N'
WHERE TABLE_NAME = '<TABLE_NAME>'
   AND COLUMN_NAME = '<COLUMN_NAME>'

Note:

Disabling this feature only affects the data that is fetched from Oracle Argus Safety Source to Oracle Argus Mart in the Next ETL. It does not affects the existing data in the MART. (It does not triggers the re-load of data for the respective CLOB columns.)