TDM - Oracle Masking Job Fails with ORA-00936 Due to Redundant WHERE Clause

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When running a masking job from the TDM Portal against an Oracle database, the job fails during execution. The following error is observed in the FDM engine logs or Portal job logs:

• ORA-00936: missing expression
• java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

Upon reviewing the generated selectSQL in the log, the query contains a double "WHERE" keyword: SELECT ... FROM "SCHEMA"."TABLE" WHERE where COLUMN_NAME in (...)

Environment

• Product: CA Test Data Manager (TDM)
• Component: Fast Data Masker (FDM) Engine / TDM Portal
• Version: 4.10, 4.11, 5.x
• Database: Oracle

Cause

The error is caused by a syntax conflict in the masking configuration. Fast Data Masker (FDM) is designed to automatically prepend the keyword WHERE to any filter criteria provided in the configuration.

If a user manually types the word WHERE into the "WHERE Clause" or "Filter" field in the TDM Portal (e.g., WHERE INTEGRATION_ID = '123'), the resulting SQL statement becomes WHERE where INTEGRATION_ID = '123', which is invalid in Oracle.

Resolution

To resolve this issue, you must remove the literal word "WHERE" from your configuration in the TDM Portal.

1. Log in to the TDM Portal and navigate to your Masking Project.
2. Edit the Masking Job or Table Configuration that is failing.
3. Locate the filter/condition field for the affected table.
4. Remove the word "WHERE" from the beginning of the string.
   • Incorrect: WHERE INTEGRATION_ID IN ('001', '002')
   • Correct: INTEGRATION_ID IN ('001', '002')
5. Save the configuration.
6. Rerun the masking job.

Additional Information

• Date Formats: If the masking job still fails after correcting the syntax, verify that any date columns involved in the masking rules have the correct date format (e.g., YYYY-MM-DD) defined in the rule parameters to ensure proper parsing by the FDM engine.
• Log Verification: Always check the selectSQL entry in the FDM logs to confirm the final SQL structure matches Oracle's requirements.

For Parquet File Masking refer to "Issue with Parquet File Masking in HASLOV when using WHERE clause with NOT IN and parentheses around the data".