We extended the product hierarchy in our demand planning module by adding custom SQL logic to the forecast batch job. The customization worked fine in our test environment, but after deploying to production (MASC 2023.1), the batch job fails with SQL syntax errors.
The error occurs during the hierarchy aggregation phase:
SELERT prod.hierarchy_level, SUM(fc.forecast_qty)
FROM forecast_detail fc
JOIN product_hierarchy prod ON fc.product_id = prod.product_id
The job runs successfully in test but fails in production with “ORA-00923: FROM keyword not found where expected”. We’ve verified the custom SQL extension is deployed identically in both environments. The base forecast job (without our customization) works fine in production.
Has anyone encountered SQL compatibility issues between MASC environments for demand planning batch jobs? We’re particularly concerned about schema differences or permission issues that might cause this behavior.
I see the typo in your SQL - it’s “SEلERT” instead of “SELECT”. That would definitely cause the syntax error. Check if there’s a copy-paste issue or encoding problem between environments. Sometimes special characters get corrupted during deployment.
Beyond permissions, I’d check the Oracle database version differences between test and production. Some SQL syntax that works in Oracle 12c might fail in 11g or vice versa. Also, look at NLS settings - character set and date format differences can cause unexpected parsing errors. Run SELECT * FROM v$version in both environments to compare.
I’ve seen this exact issue. Manhattan’s batch framework sometimes uses different connection contexts for custom extensions versus core jobs. Your custom SQL might be executing in a schema context that doesn’t have direct access to those tables. Check the batch job configuration for schema mapping settings. Also verify that your custom extension is registered properly in the batch job metadata - incorrect registration can cause context switching failures that manifest as SQL errors.
The real issue here involves multiple environmental factors that need systematic investigation. Let me address all three key areas:
SQL Compatibility Across Environments:
First, verify your actual deployed SQL doesn’t have the typo from your post. Then check database version compatibility - run SELECT banner FROM v$version in both environments. MASC 2023.1 supports Oracle 12c through 19c, but syntax handling can vary. More importantly, check if production uses a different SQL dialect or has stricter parsing rules enabled. Look at the NLS_LANG and NLS_TERRITORY settings.
Schema and Permission Differences:
This is likely your root cause. Production batch jobs typically run under a restricted service account (often MASC_BATCH or similar), while test environments often use broader permissions. Execute these checks in production:
SELECT grantee, privilege, table_name
FROM dba_tab_privs
WHERE table_name IN ('FORECAST_DETAIL', 'PRODUCT_HIERARCHY');
Verify the batch job’s connection user has SELECT privileges on both tables. If these tables are in different schemas, you need qualified names (SCHEMA.TABLE_NAME) or synonyms. Check if test has public synonyms that production lacks.
Batch Job Error Handling:
MAsc demand planning batch jobs log detailed execution context in BATCH_JOB_LOG and BATCH_JOB_STEP_LOG tables. Query these for the failed job run to get the actual SQL that executed, the connection context, and any preceding warnings. The error might occur in a different SQL statement than you think.
Implement proper error handling in your custom extension:
- Add try-catch blocks around SQL execution
- Log the actual connection user and schema context at runtime
- Include fallback logic if custom hierarchy tables aren’t accessible
- Test with the exact production service account credentials in a staging environment
Also check if your custom SQL extension is properly registered in the batch job XML configuration. Incorrect registration can cause the job to execute in an unexpected schema context. Review the batch job’s connection pool settings in masc-config.xml to ensure custom extensions use the correct data source.
Finally, enable SQL tracing for the batch job in production (set sql.trace=true in job parameters) to capture the exact execution path and identify where the context switch occurs.
Good catch on the typo - that was just a transcription error in my post. The actual deployed code has correct SELECT syntax. I’ve double-checked the deployment artifacts and the SQL is identical between test and production. The error must be environmental rather than a code issue. Could this be related to database permissions or schema object access differences?