Synchronize sap table with oracle table

[Former Member]

Hi all,

I need some ideas on how to solve this problem:

I have A table in SAP (z table, z data elements, z structures), and I have B table in oracle. Both tables are updated separately from different applications after sales is done, and each day. How can I synchronize them using web methods? Do I need table A and table B to be with identical field structure?

I am working on 4.6c and Oracle 8i...

Thanks,

Nihad

[Former Member · Accepted solution]

>

> Hi all,
> 
> I need some ideas on how to solve this problem:
> I have A table in SAP (z table, z data elements, z structures), and I have B table in oracle. Both tables are updated separately from different applications after sales is done, and each day. How can I synchronize them using web methods? Do I need table A and table B to be with identical field structure? 
> I am working on 4.6c and Oracle 8i...
> 
> Thanks,
> Nihad

Hi,

don't exactly know what you mean by "synchronizing' - well, I can imagine you could mean

BOTH tables should have the same contents in their rows and you need a process that guarantees it.

If you synchronize in that sense field structures could be different (you only focus on common columns of both tables).

However, the plain Oracle table isn't known by the Abap dictionary so you are up to using native sql

on BOTH tables.

I would wrap that in an Abap FM that you can provide to a consumer app (web or whatever).

bye

yk

[Former Member · Accepted solution]

Hi YukonKid,

Yes, by synchronizing I mean that both tables have the same content (of specific fields from both table). They will be both updated after transaction in any of two different application (SAP retail and Oracle ADF sales application). I create Z table in SAP with 3 fields, and in oracle that is table with those 3 columns and has some other columns too..

How could I maintain this synchronization process, on the table level, or on the specific field level...?

May I send u email with some details?

Thanks,

Nihad

[Former Member · Accepted solution]

ok

[markus_doehr2 · Accepted solution]

You could create a connection from the SAP system to the external Oracle database using DBCON and update/read/modify the data directly in that other database; much easier than to try to "synchronize" the data.

Markus

[Former Member · Accepted solution]

Thanks Markus,

that sounds nice, it is for one direction sap-oracle, but how should I maintain in opposite direction too,

so when that oracle table is updated, sap z table should have its fields refreshed too...

do these two tables have to be with same columns?

Thanks for your seggestions,

Nihad

[Former Member · Accepted solution]

Hi Nihad,

thanx for the data model. Unfortunatley you have to deal with 2 databases - would be better to have only one . "Synchronizing" that way can be a nightmare.

I would leave the intermediate table (anyway you have to map SAP Primary key with the ORACLE Primary key)

I want to focus on the remote access to both databases (code for table sync is obvious: native DML

from SAP side, SQL from Oracle side)

Opportunities:

SAP-side:

ABAP FM that uses a DBCON to the Oracle database using an Oracle user with write privileges

to the Oracle table

Oracle-side:

database link to the SAP database using SAPR3 user with write privileges to the SAP table

(have to ask your SAP / ORACLE Basis for preparation of the link and negotiating security policies)

If you have a link you can adjust your custom ADF application to "synchronize" the SAP table

with the usual DML statements)

non-ORACLE side

check for similar connection to the SAP DB for your preferred DB system

bye

yk

[Former Member · Accepted solution]

Hi YukonKid,

I will forget about non-Oracle (ADF) side by now.

What I focus on is SAP z table and Oracle table and their communication in both sides...

So you think it is better to ommit middle table from the data model??

NIhad

[Former Member · Accepted solution]

>

> Hi YukonKid,
> 
> I will forget about non-Oracle (ADF) side by now. 
> 
> What I focus on is SAP z table and Oracle table and their communication in both sides...
> So you think it is better to ommit middle table from the data model??
> 
> 
> NIhad

Hi,

If SAP table ZBT_SALES and Oracle table have no common primary key how you can

sync them? I mean , how you identify the same sales transaction in SAP table and vice versa in the Oracle table? Or you only add new records (INSERT) to the tables? If you have to UPDATE or DELETE you have to identify the transactions, don't you?

You said you want to add the 3 fields to ZBT_SALES so you have to identify the same transactions in both tables and sync them.

I would keep the middle table as the mapping table (additionally, if you use it as the sync-store it would be safer than manipulating your already existing custonm app tables.

bye

yk

[markus_doehr2 · Accepted solution]

> that sounds nice, it is for one direction sap-oracle, but how should I maintain in opposite direction too,

No - there is only ONE table - in your external system.

Markus

[Former Member · Accepted solution]

Yes, there is one external Oracle table.

So, I have two tables: SAP ZBT_BONUS and Oracle table.

and yes, ZBT_SALES and Oracle table must have common primary key right...

I have to maintain INSERT and DELETE for inbound and outbound process between these two tables.

Scenario 1:

New record is written in Oracle table - specific columns (or all of them) should be written (INSERT) in ZBT_BONUS.

Scenario 2:

New record is wrttien in ZBT_BONUS - all fields from ZBT_BONUS shoud be added in Oracle table.

Also, scenario 1 and 2 for DELETE.

And also, this link betwwen two tables should be online so new records from one should be

inserted/deleted in another, at the moment of change on each of them....

Do you have any suggestion?

Thanks,

Nihad

Edited by: nihad omerbegovic on Mar 12, 2009 4:11 PM

Edited by: nihad omerbegovic on Mar 12, 2009 4:18 PM

[Former Member]

Hi,

Can u help me write this FM from the SAP side?

So, we have two tables ZTABLE in SAP and Oracle table ORAC.

Let's put three columns in each of them, for example

TEL

ADRESS

NAME

where TEL field is primary key for both tables...

(in FM there shoud be abap code for insert in ZTABLE after we press some pushbutton made in sap screen painter..)

when we write new record in ZTABLE for example

112233

Street 4

YukonKid

this data shoud be inserted in Oracle table ORAC.

When we write new record in Oracle table for example

445566

New Street

Nihad

this data shoud be inseret in ZTABLE.

I must have all data from Oracle table ORAC in ZTABLE and opposite, depending on which side insertion is done.

It should be the same scenario for delete...

Can u help me from sap side? and give idea from oracle side??

Thanks a lot,

Nihad

[former_member204746]

you will need to do this with ABAP coding, because you are not allowed to change this through your ORacle database.

please refer to the ABAP forum.