ORA-00936: Missing Expression Running T2T Load with Bad ETL Data in IE6 (Doc ID 1336796.1)

Last updated on JULY 20, 2012

Applies to:

Oracle Financial Services Profitability Analytics - Version 5.1 and later
Oracle Financial Services Asset Liability Management Analytics - Version 5.1 and later
Oracle Financial Services Analytical Applications Infrastructure - Version 7.2 and later
Information in this document applies to any platform.
Oracle Financial Services Analyical Applications (OFSAA)


Symptoms

For Oracle Financial Services Profitability Analytics (PFTBI) or Asset Liability Management Analytics (ALMBI) with OFSAA 5.2, you are running a Table to Table (T2T) load for T2T_FACT_ACCOUNT_SUMMARY_LOANS.  The load batch fails and the following error is in the t2t log file in $FIC_DB_HOME/log/t2t:

2011-05-26 | 11:05:13 | t2tserver | SEVERE | | | ClsUDBConnect::executeCountQuery, Error, unable to execute count(*) query. ERROR Message: ORA-00936: missing expression

2011-05-26 | 11:05:13 | t2tserver | SEVERE | | | ClsUTableToTableLoad::directLoad, unable to fetch the count of rows for loading
2011-05-26 | 11:05:13 | t2tserver | SEVERE | | | ClsUTableToTableLoad::ExtractLoad, Error, unable to perform direct table to table load
2011-05-26 | 11:05:13 | t2tserver | INFO | | | Status from extract load ... 1
2011-05-26 | 11:05:13 | t2tserver | DEBUG | | | T2TLoad::main, INFO, Status Returned from Table To Table Loading: 1

The "Query for fetching the number of rows" in the line above these messages contains bad characters in the TO_CURRENCY_CD row.  For example:

AND FSI_EXCHANGE_RATE_HIST.TO_CURRENCY_CD = <bad characters> WHERE 1=1

Also, when the Batch fails, it failed with the following message in the Batch Monitor screen:

[10884] Error fetching the count of rows for Loading

The error prevents you from loading data into FCT_COMMON_ACCOUNT_SUMMARY.

Steps to Reproduce:

1. Go to Operations > Batch Maintenance
2. Create a new Batch
3. Create a new Task with the following details:

Component: LOAD DATA

Load Mode: Table To Table
Source Name: RESULT_AREA_T2T
File Name: T2T_FACT_ACCOUNT_SUMMARY_LOANS
Default Value: [DRCY]='USD'

4. Run the Batch in Batch Execution

The Batch fails.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms