Forum Stats

  • 3,852,452 Users
  • 2,264,105 Discussions


Replicate one merged column(Oracle float) into multiple columns in SQL Server

~AnujMohan~ Member Posts: 218 Bronze Badge
edited Jul 26, 2022 2:11AM in GoldenGate

Source Oracle / Target SQL Server

Source Sample data

select all_directions from table


RDS_FLOAT_4(104.069, 29.8188, 148.7, 252.856)

RDS_FLOAT_4(184.739, 155.408, 207.478, 150.064)


1.9Billion rows

I want to replicate this to SQL Server in 4 columns. Each comma separated value will go to separate column. Please note this is comma separated but not fixed width data

e.g. RDS_FLOAT_4(104.069,29.8188, 148.7, 252.856)

RDS_FLOAT_4<-- not needed

104.069 <----north_value 29.8188<--- south_value 148.7 <--- east_value

252.856 <--- west_value

Can you provide a sample of colmap for this case?


  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    You can do this, it is going to be messy. Assuming that your source col is called colx.

    First one is easy enough

    north_value = @strext(COLX, @compute(@strfind(COLX,'(',1)+1), @compute(@strfind(colx,',',1)-1))

    @strext is grab portion of string with begin and end, you find the '(' pos and move it by 1, thus the compute +1, then find the first ',' pos and move back by 1. Then grab that portion of the string.

    south_value = @strext(Ccolx,@compute(@strfind(colx,',',@compute(@strfind(COLX,',',1)+1), @compute(@strfind(colx,',',(compute [email protected](colx,',',1))-1) -1))))

    This second one gets harder, you can't start with pos 1 anymore as you need the second comma, so you find the 1st comma pos, feed it into another strfind to find the second. By the time you come to west_value you will need a white board.

    Create a view at the source and use function variable to populate this and get extract to read the values from it, or writes this to a temp table in the target and set triggers and thus a script to populate your target.

  • ~AnujMohan~
    ~AnujMohan~ Member Posts: 218 Bronze Badge

    Thanks.. It is working but not replicating correct data. It is a issue of datatype. Here is sample data at source

    ~~~~~~~~~~Source -Oracle 



      ( "COLX" "SYSTEM"."PCS_FLOAT_4" 

      ) ;

    Insert into ANUJMOHAN.SRC_TBL (COLX) values (SYSTEM.PCS_FLOAT_4(149.901,149.732,150.13,113.445)));

    ~~~~~Target - SQL Server

    CREATE TABLE [dbo].[target_tbl](

    [COLX1] [float] NULL,

    [COLX2] [float] NULL,

    [COLX3] [float] NULL,

    [COLX4] [float] NULL

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    Ah, it is a number type, you initially describing seems to suggest string, like "RDS_FLOAT_4<-- not needed". This string I take it never existed. So, the source is an array. Then it is easy, target_tbl.colx = SYSTEM.PCS_FLOAT_4(1), etc.

  • ~AnujMohan~
    ~AnujMohan~ Member Posts: 218 Bronze Badge

    Target is sql server and SYSTEM.PCS_FLOAT_4(1) does not exist

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    It does not need to, the source and target tables can be of different types (like oracle to sql) and different DDL. Your map statement may look something like this:

    MAP  "ANUJMOHAN"."SRC_TBL" , TARGET  [dbo].[target_tbl], COLMAP (COLX1=SYSTEM.PCS_FLOAT_4(1), ETC...

    , i.e. colmap (target column= source column, etc...) OGG knows the metadata and definition of the source table and is embedded within the OGG trails.

  • ~AnujMohan~
    ~AnujMohan~ Member Posts: 218 Bronze Badge

    Thanks K.Gan for all the help.

    Oracle Goldengate does not support replication on table ************ due to following column(s):

      Column COLX of type VARRAY.

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    Yep you are right. You will need to present a type that you can assign as is from Oracle to sql. That is another topic all together. Say you just want to convert an oracle table that has varray or any datatype that is not available in sql to an sql table. You will have the same issue. Unfortunately OGG is not going to do it for you. If you have an issue doing a conversion due to datatype unavailability then OGG will have the same issue. Google converting Oracle varray to sql.