The OBIEE write back process does a bad job in capturing the errors that happen when writing back data. And often times we need to capture the error. Here is a quick way to capture the error message.
1) Set up writeback as directed in the Oracle documentation.
2) Create an error logging table using the dbms_errlog.create_error_log package. “ERR_LOG_TABLE”
3) Modify the SQL in your inset or update statement from the writeback template to include LOG ERRORS INTO WB_POC_ERR_ORA (‘Write Back’)
For e.g my insert statement that looked like
<insert>insert into WB_INTER_INS_TAB (ACCOUNT_VAL,CAL_ONE_VAL,) values (‘@2′,’@3’) </insert>
Will change to.
<insert>insert into WB_INTER_INS_TAB (ACCOUNT_VAL,CAL_ONE_VAL,) values (‘@2′,’@3’) LOG ERRORS INTO ERR_LOG_TABLE (‘Write Back’)</insert>