article Replicate - Oracle source: Long Object Names exceeding 30 Bytes in Official Support Articles

Replicate - Oracle source: Long Object Names exceeding 30 Bytes

john_wang — Tue, 29 Aug 2023 20:46:36 GMT

In Replicate Oracle source endpoint there was a limitation:

Object names exceeding 30 characters are not supported. Consequently, tables with names exceeding 30 characters or tables containing column names exceeding 30 characters will not be replicated.

Cause

The limitation comes from low versions Oracle behavior. However since Oracle v12.2, Oracle can support object name up to 128 bytes, long object name is common usage at present. The limitation in User Guide Object names exceeding 30 characters are not supported can be overcome now.

There are two major types of long identifier name in Oracle, 1- long table name, and 2- long column name.

Resolution

1- Error messages of long table name

[METADATA_MANAGE ]W: Table 'SCOTT.VERYVERYVERYLONGLONGLONGTABLETABLETABLENAMENAMENAME' cannot be captured because the name contains 51 bytes (more than 30 bytes)

Add an internal parameter skipValidationLongNames to the Oracle source endpoint and set its value to true (default is false) then re-run the task:

2- Error messages of long column name

There are different messages if the column name exceeds 30 characters

[METADATA_MANAGE ]W: Table 'SCOTT.TEST1' cannot be captured because it contains column with too long name (more than 30 bytes)

[SOURCE_CAPTURE ]E: Key segment 'CASE_LINEITEM_SEQ_NO' value of the table 'SCOTT.MY_IMPORT_ORDERS_APPLY_LINEITEM32' was not found in the bookmark

Or (incomplete WHERE clause)

[TARGET_APPLY ]E: Failed to build update statement, statement 'UPDATE "SCOTT"."MY_IMPORT_ORDERS_APPLY_LINEITEM32"
SET "COMMENTS"='This is final status' WHERE ', stream position '0000008e.64121e70.00000001.0000.02.0000:1529.17048.16']

There are 2 steps to solve above errors for long column name :

(1) Add internal parameter skipValidationLongNames (see above) in endpoint

(2) It also requires a parameter called "enable_goldengate_replication" is enabled in Oracle. This can only be done by end user and their DBA:

alter system set ENABLE_GOLDENGATE_REPLICATION=true;

Take notes this is supported when the user has GoldenGate license, and Oracle routinely audits licenses. Consult with the user DBA before alter the system settings.

Environment

Oracle (version 12.2 and up) source endpoint for Qlik Replicate
Qlik Replicate versions 2021.5/2021.11/2022.5 and up

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

kristow — Thu, 07 Sep 2023 17:52:46 GMT

Hi @john_wang

Are you saying the only fix to allow for 30+ character column names to work in Qlik Replicate from an oracle source is for us to license Oracle Golden Gate?

Regards,

Kevin

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

Dana_Baldwin — Thu, 07 Sep 2023 18:20:56 GMT

Hi @kristow

Unfortunately, this is an Oracle limitation. They will only write 30+ character table & column names to the redo logs if GoldenGate replication is enabled on the instance, which of course requires a license. Is it possible to modify your column names to less than 30 characters?

Thanks,

Dana

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

qlikity_qlak — Fri, 09 Aug 2024 21:17:17 GMT

Hello

Just wanted to point out that we recently faced the error indicating the table name exceeded 30 bytes and followed the solution for table names as described above. After retrying, we then faced the following error:

Failed to build 'where' statement; Failed to get update statement for table RMS.XXKRO_RMS_REPL_ITEM_LOC_REP_STG, stream position 69;638579281909917580;20240724114209594052|00000000.39d289d1.00000001.000e.02.0000:844.6389658.16

After putting in a support case for help, I realized the solution to enable the "enable_goldengate_replication" parameter in Oracle was mentioned here for columns, but it appears to apply to table names as well.

There are 2 steps to solve above errors for long column name :

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

john_wang — Sat, 10 Aug 2024 04:55:53 GMT

Thank you for the update @qlikity_qlak

article Replicate - Oracle source: Long Object Names exceeding 30 Bytes in Official Support Articles

Replicate - Oracle source: Long Object Names exceeding 30 Bytes

Cause

Resolution

Environment

Related Content

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes

Re: Replicate - Oracle source: Long Object Names exceeding 30 Bytes