ORA-01723: zero-length columns are not allowed

[StreetTalender]

Hi,

I have created a migration job which does not make any modification to the source DB.

I have got this error:

   Exception in component tDBOutput_1 java.sql.SQLSyntaxErrorException: ORA-01723: zero-length columns are not allowed

I guess the problem is that the DBs are Oracle, in which ““ (empty string) is treated as null.

And I am not allowed to change anything in the source and destination DB.

With this condition, is there any workaround?

Best Regards,

[Anonymous]

Where it says....

Dynamic dynamicColumnsTmp = row5.newColumn;

....in my code. You just need to replace the "row5.newColumn" with the row and column that covers your Dynamic column. It looks like you column is called "other", but I do not know the row name.

[StreetTalender]

Thanks for your quick responce @Richard Hall​ 

Is the row name something I can find in Talend Studio?

I don't recall naming a row...

[Anonymous]

The row name is shown in the design window. For example, in the screenshot below (a random image to show this), you can see "row1", "row4", "row5", etc. That is the row name.

[StreetTalender]

Hi @Richard Hall​ ,

thanks for the help!

I attached the output of the code. As the schema is big, I just included the first 50 rows.

[Anonymous]

Thanks so much for this. This gives me enough to raise this as a bug. FYI this shows that the Dynamic Schema is picking up the required data to identify what the created table should look like, but this is not being used.

There are a couple of ways you can work around this.

1. Use a DB component to query the schema of the source DB and retrieve the DDL, then use that to build your target schema. Something like this might help (https://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable)
2. Alternatively, simple do this manually and run your existing job with the create table functionality switched off.

I will raise a Jira for this now, but if you want to raise this with Support (pointing to this Community post) they may be able to offer you a few more suggestions on how to get around this.