2 Replies Latest reply: Jan 14, 2013 1:30 AM by -joe RSS

    SQLEXEC

    902923
      Hi Experts,

      There is a requirment related with data transformation using sqlexec, environment is Oracle 11.2 to Oracle 11.2 unidirectional OGG 11.2 . in source there is a table 'account' which is having accno, name , branch columns

      the same table in target database having accno, name , branch and ONE more column TOT_BAL which is going to be replicated based on lookup table values,

      there are two lookup table in target database one is balance and another one is interest,

      the table structure is

      1. DESC BALANCE

      ACCNO

      BAL

      2. DESC INTEREST

      ACCNO

      INT_AMT

      the requirment is ,


      i have to replicate account table data from source datbase to target database but while replicating in to target database account table the extra column(TOT_BAL) value should be sum of balane.bal and interest.int_amt from above two lookup table values , can you please guide how to achive this requirement, i have tried the below one with stored procedure the replication is working fine but its adding all the column values ,


      REPLICAT PROCREP
      USERID gguser@olprod, password ********
      MAP SRC.ACCOUNT, TARGET TGT.ACCOUNT, &
      SQLEXEC (SPNAME MAX_TRANS, &
      PARAMS (v_accno = accno)), &
      COLMAP (USEDEFAULTS, TOT_BAL = @getval(MAX_TRANS.v_MAX_CRD));


      the SP is

      create or replace procedure max_trans
      (v_accno IN number, v_max_crd OUT number)
      is
      begin
      select sum(trn.bal + int.int_amt) INTO v_max_crd from balance bal, interest int where accno=v_accno; ---- here is the problem , how to get the accno value as the input from source account table accno
      end;
      /


      Thanks.
        • 1. Re: SQLEXEC
          satrap
          Why are you using @getval?

          Try as

          REPLICAT PROCREP
          USERID gguser@olprod, password ********
          MAP SRC.ACCOUNT, TARGET TGT.ACCOUNT, &
          SQLEXEC (SPNAME MAX_TRANS, &
          PARAMS (v_accno = accno)), &
          COLMAP (USEDEFAULTS, TOT_BAL = MAX_TRANS.v_max_crd);
          • 2. Re: SQLEXEC
            -joe
            replication is working fine but its adding all the column values
            All of which columns? And this should have failed:
            select sum(trn.bal + int.int_amt) INTO v_max_crd from balance bal, interest int where accno=v_accno;
            Because you probably meant bal.bal instead of trn.bal. You also have no join between the tables BALANCE and INTEREST. I therefore suspect this is a homework assignment and will stop short of providing the solution. You need to get that query working in sqlplus first. You can also use TRACE in the SQLEXEC to see (in the report file) the values of the parameters being passed.

            It's perfectly fine to use @GETVAL but as of several versions ago it is no longer required but still supported for backwards compatibility.

            Good luck,
            -joe