This discussion is archived
2 Replies Latest reply: Jan 13, 2013 11:30 PM by -joe RSS

SQLEXEC

902923 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points