Oracle Mirroring - Invalid Identifier Error

[apitcher11]

I am trying to mirror from an on-prem Oracle DB, and one of the tables I need gives me the following error:

SourceErrorCode: ReplicationError: Failed to do initial data load for table <TableName> due to 'ORA-00904: "SYS_NC00112_": invalid identifier', ErrorCode: InputValidationError"

I know this is an invalid column header error, and I tracked down the issue to a hidden system generated column named, "SYS_NC00112$". They're similar, but the column name in the error message has the dollar sign replaced with an underscore. Is this a special character limitation with the dollar sign? Is there any way around this?

[Lozovskyi]

Hello @apitcher11 , unfortunatelly it is a delta table limitation. Even in copy activity you might have the same issue, so the only way to overcome it is to have custom configuration-driven copy pipeline, where you explicitely define DML and rename this column to _.

Thank you for sharing the case. Hopefully we'll get mirroring setup upgrade in the near future.

BR, Taras

[apturlov]

@apitcher11 This issue could be related to how OneLake handles folder/file naming conventions. All mirrored tables become folders and files in OneLake file system. While the `$` is not specifically prohibited as a part of the folder/file/table name but it may cause problems when interpreted by various Fabric compute jobs. The fact that in a Fabric error message it's replaced with `_` hints that some sanitization technique could have been used. You could try and rename your table without using a `$` to verify. If it's not possible, your only options is to use a data pipeline for ingestions instead of mirroring. I cannot confirm whether mirroring allows you to choose specific columns in the table for mirroring, otherwise try and exclude the column.

That's as much as I could think of and wish you luck.

[v-prasare]

We are following up once again regarding your query. Could you please confirm if you have raised ideas with Microsoft? If not please upvote the idea in community. Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

[v-prasare]

HI @apitcher11,

this is already added as feedback/ideas, please refer below referenced URL. please upvote here as PG team will take this as a priority in future roadmap.

Add Column‑Level Exclusion Support for Oracle Data... - Microsoft Fabric Community

Thanks,

Prashanth