topic Re: CLOB field is Null in Before-Image in a change table in Qlik Replicate

CLOB field is Null in Before-Image in a change table

MoeE — Wed, 02 Jul 2025 00:38:27 GMT

Hi,

This is behaviour is present in a direct task and in a logstream task. The source database is Oracle. Targets are SQL Server and Synapse. We've seen the behaviour in both targets.

I am seeing the following behaviour:

1. A record is update in the source.

2. The After-Image has a value for the CLOB field, while the Before-Image is NULL.

I have done some reading and it is vaguely suggested that this may be intentionally how Qlik Replicate functions. However, I have not found any explicit confirmation anywhere that LOB fields in the Before-Image will appear as NULL in the target change table. I've looked through the change table documentation and the Oracle, SQL Server, and Synapse limitations, yet there is no mention of this.

Can someone please confirm whether this behaviour is indeed expected, and whether it can be changed to populate the Before-Image with the data?

Thank you.

Kind Regards,

Mohammed

Re: CLOB field is Null in Before-Image in a change table

DesmondWOO — Wed, 02 Jul 2025 08:05:42 GMT

Hi @MoeE ,

I believe this is expected behavior. The size of a LOB column can be considerable, and including the before-image would occupy more space. Additionally, since LOB columns are not used for primary keys or unique indexes, having a before-image is not useful.

Regards,
Desmond

Re: CLOB field is Null in Before-Image in a change table

MoeE — Wed, 02 Jul 2025 23:41:28 GMT

Hi Desmond,

Thanks for the answer. Is this default behaviour will all source and target endpoints?

Regards,

Mohammed

Re: CLOB field is Null in Before-Image in a change table

MoeE — Thu, 03 Jul 2025 02:27:03 GMT

Hi Desmond,

I tried switching the endpoints to a SQL Server source and an Oracle target. I was able to see the LOB data in the before image now.

So, is this limitation only for an Oracle source endpoint or SQL Server target? Is there any way to address this behaviour within Qlik Replicate?

Regards,

Mohammed

Re: CLOB field is Null in Before-Image in a change table

DesmondWOO — Fri, 04 Jul 2025 06:53:20 GMT

Hi @MoeE ,

I tested with SQL Server as both the source and target. The before-image does not contain any values for the TEXT column. Below is my table structure:

CREATE TABLE [dbo].[text01](
[id] [int] NOT NULL PRIMARY KEY,
[c1] [varchar](30) NULL,
[t1] [text] NULL,
[dt2] [datetime2](7) NULL);

CT result:

Could you send me your DDL? I would like to test it in my environment.

Regards,
Desmond

Re: CLOB field is Null in Before-Image in a change table

MoeE — Mon, 07 Jul 2025 05:24:02 GMT

Hi Desmond,

Sure, my task was from SQL Server to Oracle 19. QR version is November 2024.

CREATE TABLE AdventureWorksLT2019.dbo.CARGOSHIPSTACTEST ( SHIPID numeric(38,10) NOT NULL, SHIPNAME varchar(100) COLLATE Latin1_General_CI_AS NULL, CAPACITY numeric(38,10) NULL, OWNER varchar(100) COLLATE Latin1_General_CI_AS NULL, DESCRIPTION varchar(MAX) COLLATE Latin1_General_CI_AS NULL, CONSTRAINT CARGOSHIPSTACTEST_PK PRIMARY KEY (SHIPID) );

Here were my results.

Regards,

Mohammed

Re: CLOB field is Null in Before-Image in a change table

MoeE — Mon, 07 Jul 2025 06:48:00 GMT

Hi Desmond,

I've tried it using your schema and I did not get the before image of the column. This is interesting

However with my table I am able to get a before image.

I am not able to get a before-image at all for Oracle as a source. I've even tried John Wang's method of adding an extra column with the $BI__ prefix, however this did not work with Oracle as a source for my CLOB column.

My main objective is to confirm whether this is indeed the intended behaviour for the Oracle source endpoint. Can this be confirmed?

Thanks for the help so far.

Regards,

Mohammed

Re: CLOB field is Null in Before-Image in a change table

DesmondWOO — Mon, 07 Jul 2025 07:21:20 GMT

Hi @MoeE ,

In my environment, I can retrieve the before-image from varchar(max), but it returns blank for text. To confirm this behavior, please create a support ticket so we can check with R&D.

Regards,
Desmond