DMS load from Oracle NUMBER(38) to Aurora Postgres BIGINT fails although all values < 2,000,000,000

0

DMS load fails from Oracle NUMBER(38) source to Aurora Postgres BIGINT (INT8) target. Passes premigration assessment. All values being migrated are below 2,000,000,000 (~BIGINT limit).

The load succeeds if the target datatype is changed to Postgres NUMERIC. Inserting "102675852.0000000000" manually (using SQL) to the same column defined as BIGINT also works without error.

The full error is "ERROR: 22P02: invalid input syntax for type bigint: "102675852.0000000000" ("LOCATION: scanint8, int8.c:127", "STATEMENT: COPY "(table name)"."(column name)" FROM STDIN WITH DELIMITER ',' CSV NULL 'attNULL' ESCAPE ''").

Could work around by changing datatype after load, but the table is quite large so this takes much too long for our purposes.

1 Answer
1

By default DMS will map Oracle's NUMBER(38) to NUMERIC(38,0) on PostgreSQL compatible target (b), which should work without issues. If you pre-created the target table, mapping Oracle's NUMBER(38) to BIGINT, by default this should also work without issues but will fail on PostgreSQL if the value is larger than 9223372036854775807 as per PostgreSQL reference (a). See example below using DMS 3.4.6:

Oracle Source

CREATE TABLE sports.test_tbl 
(
 t_no       NUMBER(38)  NOT NULL,
 t_date     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
 t_code     VARCHAR2(255) DEFAULT SYS_GUID() NOT NULL,
 CONSTRAINT test_tbl_t_no_pk PRIMARY KEY (t_no) 
);
INSERT INTO sports.test_tbl(t_no, t_code) VALUES(102675852, SYS_GUID());

SQL> SELECT * FROM test_tbl;

         T_NO T_DATE                         T_CODE
------------- ------------------------------ ---------------------------------------------
    102675852 07-MAR-22 01.40.13.973615 PM   D9A5F51879C746DDE0530A101FACBE2A

PostgreSQL compatible target

CREATE TABLE test_tbl 
(
 t_no       BIGINT NOT NULL,
 t_date     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
 t_code     VARCHAR(255),
 CONSTRAINT test_tbl_t_no_pk PRIMARY KEY (t_no) 
);

After Full Load

PostgreSQL compatible target

demo=> SELECT * FROM test_tbl;
   t_no    |           t_date           |              t_code
-----------+----------------------------+----------------------------------
 102675852 | 2022-03-07 13:23:46.114686 | D9A5F51879BF46DDE0530A101FACBE2A
(1 row)

That said, it seems (I stand corrected) the behavior you are describing normally occurs when you have unbounded NUMBER type column on Oracle[]. By default DMS maps unbounded NUMBER type column to NUMERIC (38,10) on PostgreSQL compatible targets. I such cases we recommend setting below Extra Connection Attributes for the endpoints:

For Oracle as a source Endpoint

numberDataTypeScale=-2

For PostgreSQL as a target Endpoint

truncateTrailingZerosForPlainNumeric=true

References

(a) https://www.postgresql.org/docs/9.1/datatype-numeric.html

(b) https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.DataTypes

AWS
Eli DOE
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions