Oracle to Oracle -- problem with data transfer

[suvbin]

Hi Team,

We are transferring data  from oracle to oracle. In the source for a number (datatype) column the value is "null". but when it transferred to the target it is showing as number as a datatype , but the displaying some value. But actually it should show us "null". 

source : 11g 

target : 19c

DATA_PRECISION is null  , but there is a value in the target and DATA_SCALE 0

Source
Col_Name	Data_type	Data_precision	Data_scale
a	number	3	0
b	number	null	0
c	number	null	0
d	number	null	0
Target
Col_name	Data_type	Data_precision	Data_scale
a	number	3	0
b	number	33	0
c	number	33	0
d	number	33	0

Request your help here.

Thanks.

[OritA]

Hi, 

I am not sure  that I fully understood the problem. However, in general you can find the  supported data types for source and target endpoint in the Replicate users guide. For example the supported data types for Oracle source can be found  under the following sections:
https://help.qlik.com/en-US/replicate/May2023/Content/Replicate/Main/Oracle/ora_source_data_types.ht...

If you have any specific problem with a value/column not being replicated as expected, please open a case and provide the task diagnostic package with an example row that was not replicated correctly so we can further troubleshoot the problem. 

Thanks & regards,

Orit

[Heinvandenheuvel]

Isn't this what is controlled by the Oracle Source Endpoint Advanced Setting "Expose NUMBER as:" defaulting to NUMERIC(38,10)  ?

Hein.

[Dana_Baldwin]

Hi @suvbin 

Per @Heinvandenheuvel 's post, please try setting this "expose number as" field to varchar.

Hope this helps.

Dana

[suvbin]

Hi Dana,

This has already been done. It resolved the issue for the below scenario.

now it is same wherever NUMBER is with null DATA_PRECISION and null DATA_SCALE.

Now we are facing the issue for below scenario.

Now the data type is matching except two tables in which DATA_PRECISION is null but there is a value in DATA_SCALE 0

Thanks,

[suvbin]

Any update on this please 

[Heinvandenheuvel]

>> Any update on this please 

This is NOT an official support channel. You know that. If you 'need' a a formal, timely update then submit a support ticket, otherwise you'll have to content with silly folks like myself or Qlik personal when 'they feel like like it' and have nothing better to do.

Now back to the problem at hand. The subject line is "problem with data transfer" but that's not really the issue is it? Isn't it a problem with 'META-data definitions on target for NUMBER column on Oracle Source'. The DATA is transferred fine, but it's (re)presentation is not as desired. Correct? The problem is not 123 showing up as 456, but as 123.00 or visa versa. Correct? 

I'm sure you 'see' the challenge Replicate is facing right? It is a UNIVERSAL tool, which tries best it can to avoid DB specific notions. The Oracle generic 'NUMBER' which imho is more often than is the 'lazy' choice it special. So when Replicate 'sees' that coming in it has to map to 1 specific internal generic number format in order to be ready to transfer to any of a range of target DB's which are unlikely to have a 'NUMBER' type, except for Oracle.

So Replicate tries to pick something reasonable which doesn't loose too much precision, and it is nice enough to give you that choice 'expose NUMBER as'. If for some columns that is not appropriate, you'll just have to dive in and TRANSFORM those columns to a more suitable datatype. Or - less desirable - you could pre-create the target tables and let Replicate re-map to the selected type, but now you have to manage your (re)loads very carefully, truncating instead of drop+create for example.

Hope this helps,

Hein.

[suvbin]

Got it. Thank you.