topic Re: ORA-01723: zero-length columns are not allowed in Talend Studio

ORA-01723: zero-length columns are not allowed

StreetTalender — Fri, 15 Nov 2024 22:10:53 GMT

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,

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Mon, 23 Jan 2023 11:55:46 GMT

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.

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Tue, 24 Jan 2023 08:21:57 GMT

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,

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Tue, 24 Jan 2023 11:20:42 GMT

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?

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Wed, 25 Jan 2023 02:17:38 GMT

Hi @Richard Hall ,

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

thanks

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Wed, 25 Jan 2023 10:47:38 GMT

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.

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Thu, 26 Jan 2023 08:33:14 GMT

Hi @Richard Hall,

Here is my tOracleOutput configuration.

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

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Thu, 26 Jan 2023 08:42:56 GMT

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

----------------------------------------

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Thu, 26 Jan 2023 11:31:22 GMT

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.

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Thu, 26 Jan 2023 13:16:04 GMT

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?

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Thu, 26 Jan 2023 13:21:13 GMT

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.

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Thu, 26 Jan 2023 13:43:32 GMT

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...

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Thu, 26 Jan 2023 14:01:03 GMT

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.

Re: ORA-01723: zero-length columns are not allowed

StreetTalender — Fri, 27 Jan 2023 00:47:43 GMT

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.

Re: ORA-01723: zero-length columns are not allowed

Anonymous — Fri, 27 Jan 2023 11:18:54 GMT

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.

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)
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.