Creating a Mapping to perform an Update and Insert to an Oracle table with source data from Salesforce

As the post title says, in this post I have created a mapping to perform an update and insert to an Oracle table with source data from Salesforce. If you still have not created a Salesforce Data model, please refer to a previous post for reference.

Now to begin, in Designer, expand your project folder and create a new mapping

Enter a name for your Mapping

In Designer > Models accordion, select the necessary tables serving as your source and target object. In the Logical tab of your mapping, drag the tables. Example screenshot below is showing the source table which is in Salesforce and the target table which is in an Oracle database. I have added a filter in this example.

Still in the Logical tab, in the Properties > Target box, select 'Incremental Update' for the Integration Type.

One field in my Salesforce table has a data type of LONGTEXTAREA. Please refer to a previous post on how you can set ODI12c to handle such data type. 

In the Physical tab,  in the TARGET_GROUP highlighted, click on the target units. In Properties > Loading Knowledge Module, in the dropdown you either select <Default> or 'LKM SQL Multi-Connect'. 

Still in the Physical tab, click the target table. In Properties > Integration Knowledge Module, in the dropdown select the appropriate IKM. You can select the out of the box IKM: 'IKM SQL to SQL Incremental Update'. But this IKM will not work if one of the columns on your table has a CLOB data type. The IKM has a task to identify which fields are for insert or update. This involves comparing all fields in the where clause, however, this cannot be done in an SQL if one field has a CLOB data type. In this case, I have modified the IKM 'IKM SQL to SQL Incremental Update' so that only the SYSTEMMODSTAMP field will be checked by Oracle to determine if a record has been a changed. 

Save your work, execute and verify the data.