topic Re: Dynamic schema Postgres to Oracle in Talend Studio

Dynamic schema Postgres to Oracle

Petterfewf — Fri, 21 Feb 2025 08:56:38 GMT

Hi,

We are struggling to build a job using a dynamic schema to move data from PostgreSQL to Oracle. We get the following error when we run the job: java.sql.SQLException: Invalid column type.

We are using dynamic schemas in other jobs and sometimes use tJavaRow to manipulate the dynamic schema to fit our needs (see example code below). We are not sure how to troubleshoot the "Invalid column type" error. I think it's possible to manipulate the column type, but how do I know which column is causing the issue?

Any tips, on moving forward here ?

Our code to fix the problem with column lenght, and not null value...

Dynamic dyn = row1.fullskjema;

for(int i = 0; i < dyn.getColumnCount(); i++){

DynamicMetadata meta = dyn.getColumnMetadata(i);

if (meta.getLength() >4000 ){

meta.setLength(1000);

}

else{

meta.setLength(meta.getLength());

}

if (meta.getLength() ==8 ){

meta.setLength(20);

}

else{

meta.setLength(meta.getLength());

}

if (meta.isNullable() == false ){

meta.setNullable(true);

}

row2.fullskjema = dyn;

Re: Dynamic schema Postgres to Oracle

diegozecchini — Fri, 21 Feb 2025 10:33:00 GMT

Hi!
the error suggests an incompatibility between PostgreSQL data types and Oracle.

To fix it you need to know which column is causing the issue, you can modify your code to log column names and their types, I would put a

System.out.println("Column: " + meta.getName() +
" | Type: " + meta.getType() +
" | Length: " + meta.getLength() +
" | Nullable: " + meta.isNullable());

betweeen "DynamicMetadata meta = dyn.getColumnMetadata(i);" and "if (meta.getLength() > 4000) {"

Re: Dynamic schema Postgres to Oracle

Petterfewf — Fri, 21 Feb 2025 10:53:40 GMT

Hi Diegozecchini. Thank for you suggjestion, I will try this code and hopefully be able to locate the column with the incompatibility datatype.

I manage to run the job, with "forcing" all the colum to be string, and only "tune" column size.

Here is the working code.

Dynamic dyn = row1.fullskjema;

for(int i = 0; i < dyn.getColumnCount(); i++){

DynamicMetadata meta = dyn.getColumnMetadata(i);

meta.setType("id_String");

if (meta.getLength() >4000 ){

meta.setLength(3000);

}

else{

meta.setLength(100);

}

row2.fullskjema = dyn;

Re: Dynamic schema Postgres to Oracle

diegozecchini — Fri, 21 Feb 2025 10:56:57 GMT

That’s a smart workaround! Forcing all columns to id_String ensures that Oracle doesn't reject any incompatible types, and tuning the column size prevents exceeding VARCHAR2 limits.

Now that the job is running, you can start gradually reintroducing the correct data types where needed

If you have integer or decimal values stored as strings, they might affect performance or queries. You can use meta.setType("id_Integer") or id_BigDecimal where applicable.

Let me know if you need further help