12 Replies Latest reply: Mar 13, 2009 12:23 PM by Eric Brunelle RSS

Synchronize sap table with oracle table

Nihad Omerbegovic
Currently Being Moderated

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

  • Re: Synchronize sap table with oracle table
    Bernd Boecker
    Currently Being Moderated

    >

    nihad omerbegovic wrote:

     

    > 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

    • Re: Synchronize sap table with oracle table
      Nihad Omerbegovic
      Currently Being Moderated

      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

      • Re: Synchronize sap table with oracle table
        Markus Doehr
        Currently Being Moderated

        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

        • Re: Synchronize sap table with oracle table
          Nihad Omerbegovic
          Currently Being Moderated

          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

          • Re: Synchronize sap table with oracle table
            Bernd Boecker
            Currently Being Moderated

            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

            • Re: Synchronize sap table with oracle table
              Nihad Omerbegovic
              Currently Being Moderated

              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

              • Re: Synchronize sap table with oracle table
                Bernd Boecker
                Currently Being Moderated

                >

                nihad omerbegovic wrote:

                 

                > 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

                • Re: Synchronize sap table with oracle table
                  Nihad Omerbegovic
                  Currently Being Moderated

                  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

          • Re: Synchronize sap table with oracle table
            Markus Doehr
            Currently Being Moderated

            > 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

  • Re: Synchronize sap table with oracle table
    Nihad Omerbegovic
    Currently Being Moderated

    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

Actions