3 Replies Latest reply on Jul 29, 2013 5:12 PM by marie.couch

    Oracle GoldenGate INCLUDE for COLMAP

    RachelGuild

      Hi,

      I'm wondering if anyone can advise me on my issue. I have set up a PRM file, within the file i am using the INCLUDE as follows for my COLMAPS:

       

      INCLUDE ./dirprm/CORE/TEST.inc

       

      Within this file i have the following:

       

      -- List of tables (MAP statements)

       

      -- MAP <source schema name>.<table name>,

       

      -- TARGET <target schema name>.<tablename>;

       

      --

       

      MAP COREOWN.GGTEST TARGET COREOWN_LND.GGTEST, &

       

      KEYCOLS ( LND_ID ), &

       

      SQLEXEC ( ID record_lookup, QUERY "select DW_LOAD_CONTROL.lnd_id_seq.NEXTVAL lnd_id_seq_val, SYSDATE lnd_ins_ts from dual", NOPARAMS), &

       

      COLMAP ( COL1 = COL1,

       

      COL2 = COL2,

       

      LND_ID = @GETVAL (record_lookup.lnd_id_seq_val),

       

      SRC_LOC = @TOKEN ("TK-DBNAME"),

       

      ORIG_OP = @GETENV ("GGHEADER","OPTYPE"),

       

      ORIG_OP_TS = @GETENV ("GGHEADER","COMMITTIMESTAMP"),

       

      NEWCOL1 = NEWCOL1,

       

      ORIG_OP_USER = @TOKEN ("TK-ORIG_OP_USER"),

       

      LND_INS_TS = @GETVAL (record_lookup.lnd_ins_ts) );

       

      MAP COREOWN.GGTEST2 TARGET COREOWN_LND.GGTEST2, &

       

      KEYCOLS ( LND_ID ), &

       

      SQLEXEC ( ID record_lookup, QUERY "select DW_LOAD_CONTROL.lnd_id_seq.NEXTVAL lnd_id_seq_val, SYSDATE lnd_ins_ts from dual", NOPARAMS), &

       

      COLMAP ( COL1 = COL1,

       

      COL2 = COL2,

       

      LND_ID = @GETVAL (record_lookup.lnd_id_seq_val),

       

      SRC_LOC = @TOKEN ("TK-DBNAME"),

       

      ORIG_OP = @GETENV ("GGHEADER","OPTYPE"),

       

      ORIG_OP_TS = @GETENV ("GGHEADER","COMMITTIMESTAMP"),

       

      NEWCOL1 = NEWCOL1,

       

      ORIG_OP_USER = @TOKEN ("TK-ORIG_OP_USER"),

       

      LND_INS_TS = @GETVAL (record_lookup.lnd_ins_ts) );

       

      As soon as i add a second table to this, only the second table gets processed via the replicat. Looking at the STATS on the extract, the inserts and updates for the first and second tables are coming through, however if you look at the replicat it only replicates the second table.

       

      Apart from adding this back into the PRM file, can i get around this issue?

       

      Kind Regards

      Rachel

       

       

        • 1. Re: Oracle GoldenGate INCLUDE for COLMAP
          marie.couch

          Rachel,

           

          I can't see anything in the two map statements that would make the introduction of the second table MAP cause Replicat to skip the first. Can you do a SEND REP <replicat name> REPORT, and then paste the output of the entire report into this thread? I'd like to see the stats and the rest of the Replicat parameters.

           

          Thanks,

          Marie

          • 2. Re: Oracle GoldenGate INCLUDE for COLMAP
            RachelGuild

            Hi Marie,

             

            I'm getting the error

            Duplicate stored procedure name (record_lookup).

             

            Sorry i didn't realise this earlier because i've written a routine to automatically restart GG when an error occurs, although this should only apply to when a DDL event occurs it seems to execute whenever an error occurs, another error in my setup that i will fix.

             

            My question is, the stored procedure name, do they have to be unique for each COLMAP? it looks like they do. Can i pull the stored procedure to execute outside of the COLMAP statement so it is only included once in my parameter file?

             

            Kind Regards

            Rachel

             


            • 3. Re: Oracle GoldenGate INCLUDE for COLMAP
              marie.couch

              Rachel,

               

              Unfortunately, this is the case that the stored procedure names much be unique, so they can't be reused across multiple MAP or TABLE statements. Here is the KM that discusses this:

               

              OGG Replicat Abended With OGG-00303 Duplicate Stored Procedure Name (Lookup) Error (Doc ID 1386883.1)

               

              There is an enhancement request that was made to have this limitation removed to enable a user to include the same stored procedure in multiple MAPs but this functionality is not currently targeted for a particular release.

               

              Best regards,
              Marie