Post stops and you see the following messages in the event_log:
[286] 08/12/08 08:39 Notice: ORA-01850: hour must be between 0 and 23. [sp_opst_mt(osp)/15855]
[286] 08/12/08 08:39 Error: Unexpected Oracle error: ORA-01850: hour must be between 0 and 23. (object name: "OWNER"."TABLE") [sp_opst_mt (for o.SID-o.SID queue queuename)/15855]
08/12/08 08:39 Process exited sp_opst_mt (for o.SID-o.SID queue queuename) [pid = 15855] - exit(1)
Turn on the post debug and you will see messages similar to the following in the opo log: (grep for 1850)
008-08-12 13:50:44.095584 4221 5 error(table:"OWNER"."TABLE") oracle rc=1850 ../src/opst_mt/sql.c:8607
08-08-12 13:50:44.095714 4221 5 BreakLines: update "OWNER"."TABLE" t set "COL1_TS"='1203-001175717',
08-08-12 13:50:44.095714 4221 5 "COL2_TS"='0803-2005185717',"COL3_TIMESTAMP"=
08-08-12 13:50:44.095714 4221 5 '08122008113923' where rownum = 1 and "REF_NO"='0065690'||
08-08-12 13:50:44.095714 4221 5 '625' and "COL1_TS"='08032005175717' and "COL2_TS"=
08-08-12 13:50:44.095714 4221 5 '08032005175717' and "COL3_TIMESTAMP"='08112008103656';
The COL1_TS value is wrong '1203-001175717'. It only has three digits as year (001) instead four, which then lead Oracle to post '75' as hour, which in turn raises the Oracle error.
Capture parses the negative year wrong.
-0001 shows up in ODR_RECORDS as -001 for year
5: [20] length=14 1203-001175717
So post gets ORA-01850
Resolution:
Update to SharePlex version 6.1.x. In this version the fix CR58504 is included.
Workaround:
Have post continue on error and fix the individual rows with compare/repair or export/import
1. set param SP_OPO_CONT_ON_ERR according to Reference Guide OR SOL277 and have post ignore 1850 errors
2. Rows which can't be applied because of ORA-1850 will be written to the $SP_SYS_VARDIR/log/SID_errlog.sql
3. Resync data
