Create table does not replicate due to ORA-01858 (4338078)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

Create table does not replicate due to ORA-01858
Description

post fails to replicate create table as select with ORA-01858. As the result, all the following dml on this table will cause post to stop due to ORA-00904, table does not exist.
Cause

There is date format mismatch between date values in the create table as select (CTAS) statement and shareplex nls_date_format setting.
When shareplex logs in oracle, it will issue alter session set nls_date_format='MMDDSYYYYHH24MISS'; however, date values in the ddl statement is using different format such as 'DD-MON_YYYY', a.book_date between '01-APR-2010' and '31-MAR-2011'.

Since Shareplex was not able to create the table, subsequentially all the dml on this table will fail with ORA-00904 because the table does not exist.
Resolution

Workaround:
To fix this issue, the script on the SOURCE must be modified with the following options:

1. add "alter session set nls_date_format='MMDDSYYYYHH24MISS';" at the begining of the script, and modify date values to match with Shareplex's nls_date_format.
2. using to_date function for date values in the script.
Additional Information

Must resolve ORA-00904 manually by creating this table on the target to help post moving forward. Disable_object_num does not work because the table's objectid is changed when it's created.

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title