Dynamic schema Postgres to Oracle

[Petterfewf]

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;

[diegozecchini]

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) {"

[Petterfewf]

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;

[diegozecchini]

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