Data Express – How to use Oracle INTERVAL data types in ODBC Extension

Problem: 

ODBC Extension version of Data Express for Distributed Systems does not support INTERVAL data types because Oracle ODBC drivers do not support INTERVAL data types. INTERVAL data types are supported by Oracle Extension version of Data Express.  For customers who manage these data types with ODBC Extension we suggest a workaround that allows the customers to avoid  the blocking during the masking/extraction process. 

Resolution:  

The solution is based on the change of the INTERVAL data type with a VARCHAR2 data type.  We assume that we have a table SRC.AAAAA in the SOURCE database containing a column FIELD_D with an INTERVAL data type. This table is created with the statement below: 

CREATE TABLE SRC.AAAAA  (

FIELD_A  NUMBER(10,0) NOT NULL ENABLE,

FIELD_B  VARCHAR2(3 BYTE) NOT NULL ENABLE,

FIELD_C  DATE NOT NULL ENABLE,

FIELD_D  INTERVAL DAY (2) TO SECOND (0) );

The workaround includes the following steps:   

1.  Create  the table SRC.BBBBB in the same database changing the INTERVAL data type of the filed FIELD_D with the VARCHAR2 data type: 

CREATE TABLE  SRC.BBBBB  (

FIELD_A  NUMBER(10,0) NOT NULL ENABLE,

FIELD_B  VARCHAR2(3 BYTE) NOT NULL ENABLE,

FIELD_C  DATE NOT NULL ENABLE,

FIELD_D  VARCHAR2(20 BYTE) );

2.  Load data from the table SRC.AAAAA to the table SRC.BBBBB with the following query: 

INSERT INTO SRC.BBBBB (FIELD_A, FIELD_B, FIELD_C, FIELD_D)

SELECT (FIELD_A, FIELD_B, FIELD_C, TO_CHAR(FIELD_D))

FROM SRC.AAAAA;

3.  Load the table SRC.BBBBB in Data Builder via the Distributed Loader. 

4.  Work with this table running masking/subsetting/extraction as usual. 

5.  After finishing masking/subsetting/extraction process the table TARG.BBBBB will be created in the TARGET environment.

6.  Create an empty table TARG.AAAAA containing the INTERVAL data type for FIELD_D in the target database:

CREATE TABLE TARG.AAAAA  (

FIELD_A  NUMBER(10,0) NOT NULL ENABLE,

FIELD_B  VARCHAR2(3 BYTE) NOT NULL ENABLE,

FIELD_C  DATE NOT NULL ENABLE,

FIELD_D  INTERVAL DAY (2) TO SECOND (0) );

7.  Execute the following INSERT statement  to load the data from the table BBBBB to the table AAAAA located in the TARGET database :

INSERT INTO TARG.AAAAA (FIELD_A, FIELD_B, FIELD_C, FIELD_D)

SELECT (FIELD_A, FIELD_B, FIELD_C, FIELD_D)

FROM TARG.BBBBB;

At the end of the INSERT execution, the target table TARG.AAAAA  will be populated  with the masked/reduced records and you will be able to work with the INTERVAL data type field  as the one specified in the source SRC.AAAAA table.