- Newest
- Most votes
- Most comments
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
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 6 months ago