topic Re: tSchemaComplianceCheck in Data Quality

tSchemaComplianceCheck

Anonymous — Sat, 16 Nov 2024 07:39:14 GMT

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

Re: tSchemaComplianceCheck

Anonymous — Mon, 17 Sep 2018 04:04:29 GMT

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

Re: tSchemaComplianceCheck

Anonymous — Thu, 20 Sep 2018 15:06:10 GMT

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

tschemacompliancecheck.PNG

Re: tSchemaComplianceCheck

Anonymous — Fri, 21 Sep 2018 08:45:53 GMT

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

Re: tSchemaComplianceCheck

Anonymous — Fri, 21 Sep 2018 08:59:44 GMT

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.

Re: tSchemaComplianceCheck

Anonymous — Fri, 21 Sep 2018 10:13:43 GMT

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-invalid-character

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:

Is that a valid check?

Re: tSchemaComplianceCheck

Anonymous — Fri, 21 Sep 2018 17:29:27 GMT

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.

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

Re: tSchemaComplianceCheck

Anonymous — Mon, 24 Sep 2018 14:14:25 GMT

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.