tSchemaComplianceCheck

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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. 

[Anonymous]

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:

Is that a valid check?

[Anonymous]

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 

[Anonymous]

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.