Column "xxx" of type "Number (double)" from input does not match type in database

[jan_lender]

Hi!
I’m trying to create workflow that reads data from Oracle 12c table and writes it to PostgreSQL table of the same structure.
I’ve put nodes into workflow as follows:
Database Connector
(oracle.jdbc.driver.OracleDriver)
Postgre SQL Connector
Database Reader
SELECT
src.*,
ROWNUM sa_sequence_number
FROM
jadro_faes.j_tpa src

Database Writer

Tables definition (simplified, most of columns omitted)

Oracle (source):

create table JADRO_FAES.J_TPA
(
	TPA_ID NUMBER(10) not null
)

PostgreSQL (target):

create table fis.j_tpa
(
	tpa_id bigint,
	sa_sequence_number bigint
)

After I execute the workflow it fails with message:
ERROR Database Writer 3:2 Execute failed: java.lang.RuntimeException: Column “tpa_id” of type “Number (double)” from input does not match type “int8” in database at position 0
ERROR

I believe there must be a node for adjusting or casting column values or better a way how to read NUMBER(10) value from Oracle DB as an Integer value in KNIME.

Thanks in advance for any kind of help.

[ipazin]

Hi!

I don’t think there is a way to read type NUMBER() from Oracle as an Integer. If there is I would like to know it! I believe you have to change(cast) it in Knime. After Database reader node use Double to Int node and then it should work.

In cases like this I used numeric type in PostgreSQL

Br,
Ivan

[jan_lender]

Hi Ivan!
Perfect! It works like a charm after I added a Double to Int node to my workflow. Thank you!
I believe numeric type in Postgre would work as well but I rather solved this particular issue to get myself prepared for next time it occurs.
Jan

[ipazin]

Excellent! You are welcome
Ivan