tSchemaComplianceCheck

Six Stars

tSchemaComplianceCheck

Hi all,

 

using the tSchemaComplianceCheck component, as I enable the "Validation Rule" it throws an exception that is: “java.sql.SQLSyntaxErrorException: ORA-00911: invalid character”.

 

From the Stack Trace, it seems to be related to this line of code: “  String dbquery_tOracleInput_tJoin_output_row5 = "select * from MASTROC.PROVA_DATA_QUALITY_UNIQUE;”  ”.

 

My Job is doing a connection to an Oracle DB and then with a tDBInput I make a SELECT query. From that output, I have linked the tSchemaComplianceCheck which has a validation rule linked. 

 

The steps I followed are those in the link at: https://help.talend.com/reader/cLOzjXXJYu8HAIolzYu4WQ/7vIRhoxP1PmD439tIyGsrw

 

Any suggestion?

 

Thanks

Moderator

Re: tSchemaComplianceCheck

Hello,

Would you mind posting your job design screenshot on forum which will be helpful for us to address your issue.

Please mask your sensitive data.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: tSchemaComplianceCheck

Hi Sabrina, 

 

yes please find it as attached.

 

I don't know how to insert the image "in line", so you can find it to the right. 

 

EDIT: I just found out how to insert the screenshot below Smiley Happy 

tschemacompliancecheck.PNG

Moderator

Re: tSchemaComplianceCheck

Hello,

Did you use dynamic schema feature in your job? 

Are you able to run your query against an Oracle DB to see if an identifier starts with ASCII character?

Best regards

Sabrina

 

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: tSchemaComplianceCheck

As long as I use the component with its "out-of-the-box" features it works fine.

 

Also the query against the DB works fine.

 

The issue comes as we enable the Validation Rule. Also, we're following the documentation at the link: https://help.talend.com/reader/cLOzjXXJYu8HAIolzYu4WQ/VYmDpwDBy6EMpPYPaXasgw

 

The only difference is the column we're checking is the ID column while in the example from the link it is any column. 

Six Stars

Re: tSchemaComplianceCheck

I guess I found the issue. The component generates this line of code: "select * from TEMP_TABLE;"

 

Maybe that ";" is causing the issue. 

This is what is suggested from other communities:

https://community.oracle.com/thread/2377506

https://stackoverflow.com/questions/11855799/understanding-mysterious-oracle-jdbc-errors-ora-00911-i...

 

Everyone suggests: "Try removing the semi colon from the end of your SQL statement."

 

But how can I remove that semi colon in Talend?

 

I tried these steps:

1) switch to code view

2) open the code with default editor

3) I removed the semi colon 

4) right click and click on "Run As Java Application"

 

This is what I get:

invalid_character.PNG

 

 

 

Is that a valid check?

 

 

 

Employee

Re: tSchemaComplianceCheck

Hi,

 

     You do not have to go to code view to remove the semicolon. Go back to the designer view and log inside the DB component  query section. Here you have to give the query without any semicolon.

image.png

 

Another recommendation is not to use select * in your query. Please be specific about the list of columns of the table so that the tool can understand and can correctly map to the corresponding schema.

 

If the reply has helped to resolve your issue, could you please mark the topic as solution provided? Kudos are also welcome.

 

Warm Regards,

 

Nikhil Thampi 

 

 

Six Stars

Re: tSchemaComplianceCheck

hi Nikhil,

 

unfortunately what you suggest doesn't apply to our case. 

 

As stated above, it is the component that generates that query. This means that it is the tSchemaComplianceCheck that generates that code automatically ("select * from TEMP_TABLE;") . Just to give you more details, that "Temp_Table" that you see in the query, is the table that is used from the Validation Rule to check that the schema is compliant. 

 

I hope it clarifies the scenario. 

 

I also tried providing as additional parameter to the JDBC connection, the "allowMultipleQueries = true" but it didn't solve the issue. 

 

 

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Introduction to Talend Open Studio for Data Quality

Find out about Talend Open Studio for Data Quality

Watch Now

Enabling Data Governance

Learn how to enable Data Governance

Watch Now

The Definitive Guide to Government Data Quality

Take a peek at the definitive guide to Government Data Quality

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog