Timestamp issue in oracle

harsh2 — Thu, 25 Apr 2024 11:57:54 GMT

Hi Qlik Team,

Source : Microsoft SQL Server

Target : Oracle

Getting following error while capturing CDC while using Batch optimized apply

Error : Invalid timestamp value in table 'USER1.attrep_changes050CD0AC_0000001' column 'col2'; the value will be changed to '~{AwAAAK1+/hLLxSTxKFlEHbyAduwSuSAXDWOomhCCeqPAFTg3}~'

Logs :

00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]T: Finished applying bulk changes for tables (bulk_apply.c:6100)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]T: Going to start applying bulk changes for table '2' (bulk_apply.c:6026)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]T: Start handling table 2 which is in the bulk (bulk_apply.c:5257)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Apply UPDATE in Bulk. Event - 'UPDATE (3)' for table 2, stream position '0000003D:000184F8:0002' (bulk_apply.c:5045)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Enter: bulk_apply_i_create_datarecord_from_update (bulk_apply.c:4153)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Enter: oracle_bulk_handle_insert_to_net_changes (oracle_endpoint_bulk.c:370)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]T: INSERT statement for table 'USER1.attrep_changes050CD0AC_0000001' was not found in the pool, going to allocate a new statement (statement_manager.c:1892)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: oci_stmt_mng_create_statement: Batch size for table 'USER1.attrep_changes050CD0AC_0000001' is '1000' (oracle_endpoint_imp.c:703)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: ar_oci_create_statement. Create and bind the statement 'INSERT INTO "USER1"."attrep_changes050CD0AC_0000001"("seq","col1","col2","ind1","ind2","seg1") values (:1,:2,:3,:4,:5,:6)' batch size is '1000' (ar_oci_stmt.c:1543)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Resolve bind type and length using source column 'seq' (ar_oci_stmt.c:970)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Resolve bind type and length using source column 'col1' (ar_oci_stmt.c:970)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Resolve bind type and length using source column 'col2' (ar_oci_stmt.c:970)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Resolve bind type and length using source column 'ind1' (ar_oci_stmt.c:970)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Resolve bind type and length using source column 'ind2' (ar_oci_stmt.c:970)
00017412: 2024-04-25T16:24:22:723736 [TARGET_APPLY ]V: Resolve bind type and length using source column 'seg1' (ar_oci_stmt.c:970)
00017412: 2024-04-25T16:24:22:724739 [TARGET_APPLY ]V: ar_oci_stmt_set_param_value. Set value for column 'seq' table 'USER1.attrep_changes050CD0AC_0000001' (ar_oci_stmt.c:395)
00017412: 2024-04-25T16:24:22:724739 [TARGET_APPLY ]V: ar_oci_stmt_set_param_value. Set value for column 'col1' table 'USER1.attrep_changes050CD0AC_0000001' (ar_oci_stmt.c:395)
00017412: 2024-04-25T16:24:22:724739 [TARGET_APPLY ]V: ar_oci_stmt_set_param_value. Set value for column 'col2' table 'USER1.attrep_changes050CD0AC_0000001' (ar_oci_stmt.c:395)
00017412: 2024-04-25T16:24:22:724739 [TARGET_APPLY ]W: Invalid timestamp value in table 'USER1.attrep_changes050CD0AC_0000001' column 'col2'; the value will be changed to '~{AwAAAK1+/hLLxSTxKFlEHbyAduwSuSAXDWOomhCCeqPAFTg3}~' (ar_oci_stmt.c:507)
00017412: 2024-04-25T16:24:22:733735 [TARGET_APPLY ]V: ar_oci_stmt_set_param_value. Set value for column 'ind1' table 'USER1.attrep_changes050CD0AC_0000001' (ar_oci_stmt.c:395)
00017412: 2024-04-25T16:24:22:733735 [TARGET_APPLY ]V: ar_oci_stmt_set_param_value. Set value for column 'ind2' table 'USER1.attrep_changes050CD0AC_0000001' (ar_oci_stmt.c:395)
00017412: 2024-04-25T16:24:22:733735 [TARGET_APPLY ]V: ar_oci_stmt_set_param_value. Set value for column 'seg1' table 'USER1.attrep_changes050CD0AC_0000001' (ar_oci_stmt.c:395)
00017412: 2024-04-25T16:24:22:733735 [TARGET_APPLY ]T: Start applying of 'UPDATE (3)' events for table 'dbo'.'Transaction' (2). (bulk_apply.c:2903)
00017412: 2024-04-25T16:24:22:733735 [TARGET_APPLY ]T: Bulk UPDATE statement for table 'USER1.Transaction' was not found in the pool, going to allocate a new statement (statement_manager.c:2016)
00017412: 2024-04-25T16:24:22:733735 [TARGET_APPLY ]T: Going to run update statement, from seq 2 to seq 2, statement 'UPDATE ( SELECT "USER1"."Transaction"."TransactionID" as "col1", CASE WHEN "USER1"."attrep_changes050CD0AC_0000002"."ind1" = 1 THEN "USER1"."attrep_changes050CD0AC_0000002"."col1" ELSE "USER1"."Transaction"."TransactionID" END as "val1", "USER1"."Transaction"."Description" as "col2", CASE WHEN "USER1"."attrep_changes050CD0AC_0000002"."ind2" = 1 THEN "USER1"."attrep_changes050CD0AC_0000002"."col2" ELSE "USER1"."Transaction"."Description" END as "val2", "USER1"."Transaction"."Category" as "col3", CASE WHEN "USER1"."attrep_changes050CD0AC_0000002"."ind3" = 1 THEN "USER1"."attrep_changes050CD0AC_0000002"."col3" ELSE "USER1"."Transaction"."Category" END as "val3", "USER1"."Transaction"."Amount" as "col4", CASE WHEN "USER1"."attrep_changes050CD0AC_0000002"."ind4" = 1 THEN "USER1"."attrep_changes050CD0AC_0000002"."col4" ELSE "USER1"."Transaction"."Amount" END as "val4", "USER1"."Transaction"."Changes" as "col5", CASE WHEN "USER1"."attrep_changes050CD0AC_0000002"."ind5" = 1 THEN "USER1"."attrep_changes050CD0AC_0000002"."col5" ELSE "USER1"."Transaction"."Changes" END as "val5" FROM "USER1"."Transaction", "USER1"."attrep_changes050CD0AC_0000002" WHERE "USER1"."Transaction"."TransactionID"="USER1"."attrep_changes050CD0AC_0000002"."seg1" AND "USER1"."attrep_changes050CD0AC_0000002"."seq" >= :1 and "USER1"."attrep_changes050CD0AC_0000002"."seq" <= :2 ) tempview
set "col1" = "val1",
"col2" = "val2",
"col3" = "val3",
"col4" = "val4",
"col5" = "val5"' (oracle_endpoint_bulk.c:805)
00017412: 2024-04-25T16:24:22:756734 [TARGET_APPLY ]T: After get rows affected, rows affected 0 (oracle_endpoint_bulk.c:816)
00017412: 2024-04-25T16:24:22:756734 [TARGET_APPLY ]T: Failed to execute update statement:
~{BAAAANOx7ItEPVLD96J+5D5SiazliQyKU/iLkL07AajCMWNHekyF7cEZtu6vig4/oPJw3u9y7BZw+Ftd0ygcv8yDOPA2TfrKpeyS5+bEe8gRJsrMrqAbcrJqPZ7SpjYipZlUKwC2sN77gX3hJGl1aGB1bUy3z9q3QHHcxHhA38ra9+jsbxbUGx3D2+x+bhyLEu7XnTpqI+8UagCJ7iMg1bCwog3uRzibx9BXGPNXxhP8tMCL9KSiELSKExQuNQYDHrpn++DSHf6XHOd/qacSj/sBPJ4bTe4db0Rkc8ZnkTqHQzwNmNlUK3e7xnxEYkNBr9kndMC/4wJtg3zY9bQuEizRBNXfY2Geavq6/GbNJedr4hn2CbLBALuyJ6XA++FqukWCX8wJmyUujrjxXdtTy2oSBDTi980hucDNXeGsClrXEJJqbTYxt8Jro2aOW0qv5EVlB+OeDwpYJr3g0Vjmcb13akiJCi/YQBt9YY0pSMUAO6ef4amD8SUgUdNpKx/0cwD05s9vvWaouzI7IsmsswomqFcQJUwMv7xJ1zbDuhjqi9KxxSqAUlcc+yocY8WMBPFaNaJeBCO4EtWer49F50m6Hmmebe+Lokx32rZcytTG9uxG8o/JsLnWFGbXcLFXszooqvZcbYq2q8Vp6z82Eq6TGALSs8ENSIAutUAeknZ0K3TVjqRqmDL9WsxMRSVp+c0Tj85aOQkqg4n5fw404QGqvDItWJnUcg1XvmBYDt9nOspxfxlImLdkPg2GI1Aarnuz+xldAZoOCAXt5Lvjx9ADUk+sZHjv8CAzFTRcmdl+nig88a1PwP/SfZWpHgMNi7Cqs1UXu3fMgzIfg7wLmmgSx/lKrL15lVPCce8bbTqNcr/rHiymQepauVU9qPcj4lQvrakXpeVOC8BPekxizINorx6JDksIsp8Ahy7dJ4Vbk0/6f/pOHjdKA8IEPUdAS9NtWMqcwVeQATHQ0jV8+NrVZUdL47cARkmsG5cX3IZQKep/Dey1bksRjNpWZoDPfZZ36BNt0xwxMXOQniIhmZFYFnxqJ/p63jIBIj69BcO3suujfMFyf/Euquz47ZBQqmWRFLkG5zZa19Fo2pQINRjqoQg7Fc4jTjv6dnN+yq7cqV0VL5R2HBC6W/sw63IiUs5HhXF/5e6yfNcpU4dmANqn+vtQhmx1GFd6ZlOY+pC3yaMHWmgR7JC6b6j+M557cbt0wH+huXcS8IDSRNjKJgJTLS5hguLKELDwDE1fiSLAZPFNepuMmcTOhGYYfrgzVje9fEBY1rmgh9aJITnKnljv9m5/ivLrk7E4Rvm7umHPVMeH8DjUtbSMMDbkyKw9pODbXSUWvVaNC3JNmFWjC6we2TiTseMANf3RPjzQ6cG0Q81xM3fIbKwXVqeGAa99rqs66eWIJA9LSV2WEkV3z6G45ToxjQEkIyHwn/rFGVBzrADYVTrImvPynSxCdnIS0LqowoL549J619kbtOxGSRTA/L+x7FXi8OOkWTbSt2Tf+FzYjsEWXx6DtH6pH5XBbtwT7AwBOSerF6znth69/OLMzgLt9NVzeb4FAij/RJvK92UMRJWmm9/kL4L7HBCEgXiVK7/8Zf2a2GfvGoZvsKXxatSj/zQthS7MZA1pUn9A+UxaONamfIxb1IDdtNFr9iOj0b75I74G3nmnP8ffFjp3SHmLVcIhuCuiq5kgfTmIgdTUG6Dlmy2mfLYND1egtAV+8ubOt+pXVQdseLV/ggKY1BSu0W0h86zjQZRwl9QXzm2uxeSpufMhIX5lxc70mifr1C6Ya7qADAeDg35eDIVA273ibM9TpntX86RxglxEPX27oMWAhSaxbJpiRpsnv/oW5Przc8Nxs8iESwE/XXTKJjiAwHOo/QAy/YiLPCi6/HumuFWK/HUVwSy+KC2oY6JygUw8kOGbTxikmBeBM1z4ikUUmgc6Cj3vT+AHyot4dSfw9aTaO1QPhWjPF7KW9OGu2L9Jn7wVo4dBoCGHZuYHoZ4=}~ [1022510] (oracle_endpoint_bulk.c:825)
00017412: 2024-04-25T16:24:22:756734 [TARGET_APPLY ]T: Failed to apply UPDATE (3) for table 2 [1020427] (bulk_apply.c:3013)
00017412: 2024-04-25T16:24:22:756734 [TARGET_APPLY ]I: Bulk apply operation failed. Trying to execute bulk statements in 'one-by-one' mode (bulk_apply.c:2430)
00017412: 2024-04-25T16:24:22:756734 [TARGET_APPLY ]T: Going to start applying bulk changes for table '1' (bulk_apply.c:6026)
00017412: 2024-04-25T16:24:22:756734 [TARGET_APPLY ]T: Start handling table 1 which is in the bulk (bulk_apply.c:5257)
00017412: 2024-04-25T16:24:22:760732 [TARGET_APPLY ]V: Going to send all loopback table events. (bulk_apply.c:4703)
00017412: 2024-04-25T16:24:22:760732 [TARGET_APPLY ]V: Handle loopback table INSERT event (bulk_apply.c:4722)
00017412: 2024-04-25T16:24:22:760732 [TARGET_APPLY ]V: Enter: oracle_endpoint_handle_update (oracle_endpoint_apply.c:1271)
00017412: 2024-04-25T16:24:22:760732 [TARGET_APPLY ]V: Update Statement: ~{BQAAAP4b/BQ93/mfJpwgx4pj9Bvcs99TvA6SErC+s8LgRZ5GOrrcObF7YXraLG/F85YH8Y158Gk9RDr5cC5NZ5GqvNla6mlYDVGVB6tpWOgr5wHv/DsIYmCL6xow1a2g4+jreXHDV+cy8X5blwBDhV7x6+KpW4lrftlUWEPrgQdaoTMPtaj3zBTSohkknIsdD+k6tnGXoBsdoFO+yq9QbOtBSmA05F927avUkHmTS/atqo8kx6tSeV9+Jto+3Se+bRKI7JA1ZAxbAkHMMvreIaQHdX2Yh0lnoqGEAuGzcL4Y2+8hDZoTN4q+sLrXPFvKhYWtiPsUJ57cd/A1MFEja5eW4nfCJTxuR5OTSjRzeFsv269V2N2alULhk2wx5SlmvQu4fzAR+ME3DbFAPys8rHozD6XpsUIJvaytCDUSACocgu40lndEV1Oy+AWiEfs260/AKQ==}~ (oracle_endpoint_apply_util.c:1442)

Regards,

Harsh

Re: Timestamp issue in oracle

john_wang — Thu, 25 Apr 2024 12:36:09 GMT

Hello @harsh2 ,

Thanks for reaching out to Qlik Community!

By default the task log files are encrypted, so we got some string like:

'~{AwAAAK1+/hLLxSTxKFlEHbyAduwSuSAXDWOomhCCeqPAFTg3}~'

We may decrypt the task log files to convert them back to readable text however the data may contain sensitive user data. If you need further help please open support ticket and attach:

1. The task Diag Packages

2. Set internal parameters keepCSVFiles & keepErrorFiles to TRUE in target endpoint Advanced properties, re-create the error and attach the generated CSV files (the files locate in task sub-folder)

3. The decrypted task log

4. The table creation DDL in source DB and the demo rows to reproduce the error

support team would like to help.

Regards,

John.

Re: Timestamp issue in oracle

DesmondWOO — Fri, 26 Apr 2024 04:17:31 GMT

Hi @harsh2 ,

In addition to John's comment, please enable verbose logging on SOURCE_CAPTURE and TARGET_APPLY. You can create a new task and start from earlier timestamp to reproduce this issue.

Regards,
Desmond

topic Timestamp issue in oracle in Qlik Replicate

Timestamp issue in oracle

Re: Timestamp issue in oracle

Re: Timestamp issue in oracle