1 Reply Latest reply: Aug 15, 2014 12:15 PM by user10407165 RSS

    Golden Gate : Problem in replicating data from column having space in it's name

    MukundN

      Hello

       

      We are trying to replicate data from SQL Server 2008 Enterprise Edition to Oracle 11g R2 using Golden Gate 11g R2

       

      Now the Source database i.e.SQL Server has column names which includes - white space, forward slash, question mark etc.

      During replication I am getting errors due to these naming conventions with special characters/space

       

      For example, for column name with white space in it I tried enclosing the column name in double quotes like following :

       

      map dbo.spl_data_types3, target app_owner.spl_data_types3, COLMAP(USEDEFAULTS, First_Name = "First Name");

       

      Here I was trying to map "First Name" column on source with First_Name on the target

      But in this case the mapped source column "First Name" is treated as literal value and thus it is then inserted in to the target table as a value for First_Name

       

      I tried enclosing the column names in parenthesis as well but it produced error

      Similarly I used single quotes but that too did not work!

       

      I tried using the 'USEANSISQLQUOTES' & 'NOUSEANSISQLQUOTES' parameters in globals.prm but it did not help

      Of course I don't know if i used these parameters correctly in the parameter file as I could not find any example of it in Oracle Documentation as well as other articles on the net

      I mentioned the parameter as following :

       

      [oracle@raptkwb dirprm]$ cat GLOBALS.prm

      USEANSISQLQUOTES

       

      The 12g document suggests using double quotes in such cases but I am using 11g and not sure how to make it work

      http://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_gettingstarted.htm#i1074957

       

      Could anybody please suggest on this?

        • 1. Re: Golden Gate : Problem in replicating data from column having space in it's name
          user10407165

          Documentation says that USEANSISQLQUOTES should do the trick.

           

          Supporting case and special characters in column names

          By default, Oracle GoldenGate treats any string within double quotes as a literal. To

          support column names that are case-sensitive or contain special characters, you can use

          the USEANSISQLQUOTES parameter. USEANSISQLQUOTES enables Oracle GoldenGate to follow

          SQL-92 rules for using quotation marks to delimit identifiers and literal strings. With

          USEANSISQLQUOTES enabled, Oracle GoldenGate treats a string within double quotes as a

          column name, and it treats a string within single quotes as a literal. This support applies

          globally to all processes in the Oracle GoldenGate instance. For more information about

          usage and limitations, see USEANSISQLQUOTES in the Oracle GoldenGate Windows and UNIX

          Reference Guide.

           

          Try maybe putting parameter USEANSISQLQUOTES right before your mapping.