Skipping tables which have been failed to migrate

[StreetTalender]

Hi,

I have created a migration job that migrates a whole schema including all its tables.

However, there are some errors in the job, so I want to skip those tables which have errors, and output which tables have failed to migrate, so that I can create jobs for those tables individually.

Can anyone help me figuring out how to do it?

In my job, I use tDBTableList, in which it stores table names in a global variable to pass to tOracleInput.

Errors I have:

• in tDBinput, ORA-00942: table or view does not exist
• in tDBoutput, ORA-01723: ORA-01723: zero-length columns are not allowed

Job design:

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

tOracleConnection1-oncomponentok-tOracleConnection2

|onsubjobok

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

|onsubjobok

tOracleClose1--oncomponentok--tOracleClose2

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

Thanks in advance.

[Anonymous]

Hi,

Thank you for reaching out!

regarding this error message: ORA-00942: table or view does not exist

This could happen for one of these reasons:

- Referencing a table or view that does not exist

- Using an unauthorized synonym

- Using an expression of view where a table is required

- Attempting to use a table without proper permission or privilege

Please verify these points using this article:

https://www.tekstream.com/resource-center/ora-00942-table-or-view-does-not-exist/#:~:text=You%20may%....

As for ORA-01723: ORA-01723: zero-length columns are not allowed

Please try the below workaround to increase the dynamic schema length to more than 100 (default max is 100) and let me know your observations:

- In the Talend Studio, click the Window button on top menu bar.

- select Show View and then scroll down to Navigator and then double click it

- go to Navigator --> Java --> src --> main --> resources --> xmlMappings then edit the mapping .xml file

- look for type="VARCHAR" & defaultLengh="200" then change it to type="VARCHAR" ignoreLen="true"

- then save it then run the job, it will insert data has length more than 100 to the database.

Best,

[StreetTalender]

Hi @Yussef Saleh​ ,

Thanks for your reply!

Regarding ORA-00942: table or view does not exist,

the execution log from TMC does not show any info to figure out which table is the problem. as I am using dynamic schema which handles thousands of tables, I cannot go through all tables to find out which table is the problem.

Could you let me know a way to find that out?

As for ORA-01723: ORA-01723: zero-length columns are not allowed,

Im my Talend studio, I could not find “Java“ folder under Navigator. (Also Navigator was marked as “depricated“, if that matters)

Could Java folder be anywhere else?

Best,

[Anonymous]

Hi

Go to the the Day of the tip page and read the tip of Sep 27, 2022, this tip shows you how to catch the exception, output the current table name if there is an error occurs, and continue to execute the next table.

Regards

Shong

[Anonymous]

I'd consider creating 2 levels.

MASTER job would list the tables you want to migrate.

WORKER would do the actual lift & shift.

Inbetween the 2 you can use Iterate link + have the ability to configure the number of threads to be used. (To make things faster.)

Then it's only letting the child job die/crash, and OnSubjobError on the parent to trigger a tFixedFlowInput (tablename) -> tFileOutputDelimited (failures).

Or raising a FATAL log message that you can redirect to a dedicated log4j appender for logging errors.

I think both of your errors are related to quotes.

SELECT * FROM FOO; SELECT * FROM foo; SELECT * FROM FoO; --> equivalent

But if you have a table defined as: FoO yuo can only query it by:

SELECT * FROM "FoO"; (so quoting is necesary)

Same is true for the columns:

If I have 2 columns: "HI," and "HOLA"

SELECT HI, , HOLA FROM TABLE -> 0 length column.

SELECT "HI," , "HOLA" FROM "TABLE" is what you want.

Maybe enable debug logs to see the queries executed by Talend against your database.

There should be no need to create custom jobs. I migrated a million different tables using Dynamic schema between different database types.

[Anonymous]

Worth adding a tWarn / tJobLog to dump the tablename you're working with/on.

[StreetTalender]

Hi shong and bgunics,​

Thanks for your suggestions!

I will try those and let you know how they go.

Best,