-
Title
compare errored out with ORA-01841 on target as shown in the *declt* log. -
Description
Customer ran into ORA-01841 when running compare on one particular table as the following declt portion shows:
000: Notice: Object ID of "owner"."tablename" is 44656
000: Oracle Error 1841: ORA-01841: (full) year must be between -4713 and +9999, and not be 0000: Error ORA-1841 calling OCIStmtExecute in de_repair_update (3753)
000: No further repairs will be applied
000: Error 1 calling de_repair_update in de_compare_client (1480)
000: Continuing to process so all the out-of-sync rows are reported
000: Error -1 calling de_compare_client (main,L2567)
Exiting -
Cause
Wrong value populated on source for the year in a DATE type field.
-
Resolution
The .sql file contains the SQL that needs to be applied to bring target table in sync with source. In our case the .sql file showed that there was an UPDATE which had a strange value for year in a DATE type field (value was 5525) :
/* source rowid='AAGaHaAAHAAAEmoAAc' */
update "owner"."tablename" set "MODIFIED_DATETIME" = to_date(
'0627 2007163419','MMDDSYYYYHH24MISS'),"SESSION_SEQ_ID" = '2010562'
,"RESULTS_PER_PAGE" = '10',"CREATED_DATETIME" = to_date('0627 2007163419'
,'MMDDSYYYYHH24MISS'),"CREATED_BY" = 'TUCWA523',"DEPOSIT_DATE_TO" = NULL
,"DEPOSIT_DATE_FROM" = to_date('0501-5525000000','MMDDSYYYYHH24MISS')
,"ITEM_PAYMENT_AMOUNT_TO" = '0',"ITEM_PAYMENT_AMOUNT_FROM" = '0'
,"TOTAL_PAYMENT_AMOUNT_TO" = '0',"TOTAL_PAYMENT_AMOUNT_FROM" = '0'
,"CHECK_NUMBER_TO" = NULL,"CHECK_NUMBER_FROM" = NULL,"BATCH_NUM_FROM" =
'5s1936',"BATCH_NUM_TO" = NULL,"MATCHING_ROWS" = '0',"CHECK_RTN_TO" = NULL
,"CHECK_RTN_FROM" = NULL,"CHECK_DDA_TO" = NULL,"CHECK_DDA_FROM" = NULL
,"SEARCH_OVERFLOW_FLAG" = 'N',"TRANSACTION_TYPE" = '0'
where rowid = 'AAAK5wAAHAAAR2sAAQ';Upon querying the source row it was found that it actually had the value 5525 in the year part of the DATE field. This could have been a result of direct load that did not validate the data populated on source. On target Shareplex could not populate this value so compare errored out. The incorrect data needs to be changed on source and compare re-run.