SQL empty string becomes an Oracle Space?

[Gary_W]

Replicate version Nov 2020.

My task simply copies a set of SQL tables to an Oracle database.  However, empty SQL string columns end up with a single SPACE on the Oracle side.  Is this normal?  Surely I don't need to add a transform function on every STRING column!?

The global transformations area only seems to provide a method for changing the datatype.

Thank you for any insight on this, it's making me crazy.  I have some massive tables to deal with.

EDIT: I think I might be getting burned since the data on the SQL side are really EMPTY STRINGS and not null strings?  However, an empty string still should not equate to a space on the Oracle side.  I tried the following transform on one column for the fun of it (gleaned from another post) and it worked, but doing this for hundreds of columns is not happening. Too bad there isn't a variable for the current column instead of having to name it specifically at least. I have to think this is a bug.

case when $col='' then null else $col end

Edit: I updated to reflect I'm dealing with empty strings and not NULL strings as originally thought.

[john_wang · Accepted solution]

Hello @Gary_W ,

First of all, you need upgrade Replicate from Nov 2020 to May 2021 to utilize the Global Rules Transformations and filters.

I'm not very sure what's your exact data types in SQL Server, so in my sample below I included both CHAR and VARCHAR data type. However please take note that the 2 lines (between ID=22 and 222) are different in SQL Server, or say, NULL is not equal empty string (''):

From your transformation expression sample, I'm guessing you want to replace '' by NULL in Oracle side. it really mixes up NULL and empty string ('') however you are almost there already. Only a bit modification on the expression and put it in Global Transformation it works fine for both Full Load + CDC, and for both CHAR + VARCHAR, and for all the tables.

The result in Oracle target side:

Expression sample:

case when $AR_M_SOURCE_COLUMN_DATA='' then null else $AR_M_SOURCE_COLUMN_DATA end

Global Transformation screen copy:

The Global rules transformation and filters are introduced from Replicate 2021.5 (May 2021), you are running 2020.11 (Nov 2020), if you want to use global transformation you need upgrade to 2021.5, or 2021.11.

Let me know if you need any additional assistance.

Regards,

John.

[Gary_W]

Thank you SO MUCH for your quick response, yes this is exactly what I need.  However, I need it yesterday!  LOL In the meantime I exported the task and created a powershell script that manipulated the JSON that was exported to basically add that same text by column (as mentioned in my first post) in order to test the timing impact.  It's running almost as fast for right now until we can get upgraded.  When that happens, I'll be back here to apply your answer!  Thanks again.

[john_wang]

Sure @Gary_W 😄

If you can get back with the performance impact comparison between column level (in 2020.11) and global level (in 2021.5) some day it's much helpful for all users. I'd like to work with you together on that. Let me know.

Thank you,

John.

[Gary_W]

Ok, not sure when we will upgrade but I will start inquiring about that tomorrow.  Thank you!

Oh, by the way it only took maybe 5 minutes longer than no expressions at all.  But that's not a scientific comparison of course.

[john_wang]

Thank you so much for your update @Gary_W 

[Heinvandenheuvel · Accepted solution]

There are 3 internal advanced Oracle target endpoint parameter which deal with empty/null strings 

Check out:

alwaysReplaceEmptyString - default is true

And maybe also 

emptyStringValue / emptyVarCharStringValue 

Hein

[john_wang]

Hello @Heinvandenheuvel ,

appreciate your information. I'd confirmed 2 of them works fine, the result is as same as the global transform.

Hello @Gary_W ,

Per @Heinvandenheuvel guide, we have another 2 perfect options (any one of them is good enough, you need NOT set both of them.):

1. Add internal parameter "alwaysReplaceEmptyString"  in Oracle target endpoint and set it value to "false" (default is true).

    This will treat empty string as Null in Oracle target.

or

2. Add internal parameter "emptyStringValue" in Oracle target endpoint  and remove the default value - a single space (it maybe invisible to you , however if you mark it, you can see it, see below picture, the blue "I" is infact a space). Remove anything in the value input text label, set its value to nothing.

I'd confirmed any one of them works fine, include Full Load + CDC, just like the global transformation. However this is much simple and it reduces the task config steps. This way is highly recommended. 

I apologize for not providing you the best solution in the past. 

Regards,

John.

[Gary_W]

Thanks for this great info.  I have implemented option 1 above instead of a function on a per-column basis and am doing some testing. 

I must say i was stumped for a bit on how to add an internal parameter.  It's not intuitive that one must first search for a non-existent parameter before it gets added.  At least in version 7.

I'll report back...

It has worked as expected and cut an hour off the run time, thank you!