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]

Can you tell us what precisely what you are trying to do here? If you are creating a table with your tDBOutput, then you will need to make sure that your column lengths are specified. But you say that you are not allowed to modify your destination DB. So we will ned to see exactly what you are trying to do here before we can get an idea as to why this is happening.

[StreetTalender]

Hi rhall, @Richard Hall​ 

thanks for your reply.

Let me explain what I want to do.

Requirement:

• The migration requirement is simple, let’s say I have a schema named “A” in the source DB, and and I want exactly the same schema “A” (including all tables and their data) in the target DB.

Background:

• I want to create a job which lets me migrate a whole schema, not only a table.
• I have about 5000 tables within 10 schemas to migrate, and I want to avoid creating 5000 jobs one by one.

Environment:

• Talend Studio 8.0.1
• source and target schema are both Oracle

I am using dynamic schema, and this is how my job looks like:

----------------------------------------​

tOracleConnection1-oncomponentok-tOracleConnection2

|onsubjobok

tOracleTableList--iterate--tOracleInput--main--tOracleOutput--oncomponentok--tOracleCommit

|onsubjobok

tOracleClose1--oncomponentok--tOracleClose2

​----------------------------------------

in tOracleTableList, I use the default where clause ("TABLE_NAME not like '%$%'") and fetch from “USER_TABLES”.

in tOracleInput, the query I use is "select * from " +((String)globalMap.get("tDBTableList_1_CURRENT_TABLE"))

This is what I set in the source schema:

​in tOracleOutput, in the “Table” field I use ((String)globalMap.get("tDBTableList_1_CURRENT_TABLE"))

Please let me know if you have any ideas.

Best,

[Anonymous]

Does the table already exist in your target DB when you write to it OR are you creating the table when you write to it?

[StreetTalender]

Hi @Richard Hall​ ,

the tables do not exist in the target DB and I create them with tDBOutput.

thanks​

[Anonymous]

OK, can you show me the configuration of your tOracleOutput configuration please?

Also, can you try out some code for me? I *think* that this may reveal a bug, but I need a little more detail. The code below is perfectly safe, it simply analyses the data held about the schema within the Dynamic Schema. You may even find it quite useful yourself. I use this in a tJavaFlex.

Start Code

//Column count variable to count dynamic schema columns processed

int columnCount = 0;  

//Row variable to count rows

int row = 0;  

Main Code

row++;

System.out.println("Row number = "+row);

if(columnCount==0){

//Set the dynamicColumnsTmp variable

Dynamic dynamicColumnsTmp = row5.newColumn;

//Cycle through the columns stored in the Dynamic schema column and add the column names

//to the ArrayList

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

   DynamicMetadata columnMetadata = dynamicColumnsTmp.getColumnMetadata(i);

    System.out.println("Column name="+columnMetadata.getName());

    System.out.println("Data type="+columnMetadata.getType());

    System.out.println("DB Data type="+columnMetadata.getDbType());

    System.out.println("Data value="+dynamicColumnsTmp.getColumnValue(i));

    System.out.println("Length="+columnMetadata.getLength());

}

//Append 1 to the rowCount

columnCount++;

}

columnCount=0;

System.out.println("");

There is a line above which displays the data value of the Dynamic Schema column. You can comment that out so that you are not sharing your private data. I am interested to see the DB Data type, Data type and Length values.

[StreetTalender]

Hi @Richard Hall​,

Here is my tOracleOutput configuration.

I will let you know once I am done with the code.

[StreetTalender]

Hi @Richard Hall​,

Could you point out to me where in my job I should put tJavaFlex in?

And which row/trigger I should use to connect it to the previous/next components?

This is my job design:

----------------------------------------​

tOracleConnection1-oncomponentok-tOracleConnection2

|onsubjobok

tOracleTableList--iterate--tOracleInput--main--tOracleOutput--oncomponentok--tOracleCommit

|onsubjobok

tOracleClose1--oncomponentok--tOracleClose2

​----------------------------------------

[Anonymous]

All you need to do is place it before the tOracleOutput component. Ensure that the "Data auto propagate" tick box is ticked. You will probably need to change the row name and column name I have used in the code as well.

[StreetTalender]

Hi @Richard Hall​ ,

as I am using Dynamic, I cannot find what sould replace "row5" in your code...

This is my schema definition, does it show the row name?