Replicate - Oracle source: Long Object Names exceeding 30 Bytes

[john_wang]

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)

Or

[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

Related Content 

Internal support case ID: # 00045265.

Qlik Replicate  

[kristow]

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

[Dana_Baldwin]

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

[qlikity_qlak]

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 :

[john_wang]

Thank you for the update @qlikity_qlak