Multiple Errors when executing ODI12c Mapping to update data in Salesforce

Error:

oracle.odi.runtime.agent.exception.ExecutionEngineException: java.sql.SQLSyntaxErrorException: [FMWGEN][SForce JDBC Driver][SForce]Table not found in statement [INSERT INTO SFORCE.C$_0AJ8IUSJ58MVU517U6TKC9FJHQK (
ID_,
OWNERID,
ISDELETED,
.....

This error occurs as a result from ODI warning:

ODI-1228: Task Create staging (C$) table-LKM SQL to SQL (Built-In)- fails on the target connection Salesforce_Stg.
Caused By: java.sql.SQLSyntaxErrorException: [FMWGEN][SForce JDBC Driver][SForce]column size is required in statement [create table SFORCE.C$_0AJ8IUSJ58MVU517U6TKC9FJHQK
(
ID_ TEXT]
.....

It is important to note that Salesforce cannot be used as a Staging area (Refer to documentation: http://docs.oracle.com/middleware/122126/odi/develop-connectivity-km/GUID-8B7FF042-1B82-4443-82D6-74D6F8B3D361.htm#ODIKM-GUID-8B7FF042-1B82-4443-82D6-74D6F8B3D361).

Although the ‘Preset Staging Location’ in the Physical tab of the Mapping is set to the source database which is Oracle, the LKM 'LKM SQL to SQL' forces to Stage the table in Salesforce.

From research and advise from Oracle Support, it is important to highlight the following:

• Starting with ODI 12c, the staging location is set by default to Target and cannot be set to the Source.
• From ODI 12.2.1.2.6, the C$ tables are going to be created in target even if a staging area is specified. This is the expected behavior of ODI.
• For Salesforce integration, only the IKMs that use binding could be used.That is, the IKMs that:
• Do not require a LKM, and
• Allow the staging area to not be set on target
• Only "IKM SQL to SQL Incremental Update" and "IKM SQL to SQL Control Append" allow staging area to be different than target, and use the binding mechanism. All the other IKMs require an LKM to be used additional to the IKM. So no binding. Not suitable for Salesforce.
• Also, the doc should mention that for the IKM (IKM SQL to SQL Incremental Update) to work, the IKM FLOW_TABLE_LOCATION option should be set to "staging" and FLOW_CONTROL is set to False.

Taking into consideration the above facts, when creating a mapping to update a record in Salesforce, do the following:

• In the target Salesforce table: Logical > Properties > Attributes, make sure to untick the check box for update of the audit columns LastModifiedDate, CreatedById, IsDeleted, SystemModstamp, CreatedDate, LastModifiedById as well as Id. 

Otherwise, you will be prompted with this error during execution: 

java.sql.BatchUpdateException: java.sql.BatchUpdateException: [FMWGEN][SForce JDBC Driver][SForce]Unable to create/update fields: LastModifiedDate, CreatedById, IsDeleted, SystemModstamp, CreatedDate, LastModifiedById. Please check the security settings of this field and verify that it is read/write for your profile or permission set.

• In the Logical > Properties > Target, select "Incremental Update" on the dropdown.

• In the Physical > [Your target table] > Properties > Integration Knowledge Module, select "IKM SQL to SQL Incremental Update". Change the option for FLOW_CONTROL to False and FLOW_TABLE_LOCATION to STAGING.

• Save and Execute.

ODI12c Error oracle.odi.runtime.agent.invocation.InvocationException: oracle.odi.core.exception.OdiRuntimeException: java.sql.SQLException: [FMWGEN][SForce JDBC Driver][SForce]The configuration options used to open the database do not match the options used to create the database.

If experiencing below error:

oracle.odi.runtime.agent.invocation.InvocationException: oracle.odi.core.exception.OdiRuntimeException: java.sql.SQLException: [FMWGEN][SForce JDBC Driver][SForce]The configuration options used to open the database do not match the options used to create the database. 

Cause:
The error existed after I created a second connection to the same Salesforce instance, and modified the connection URL. This error is returned when using a connection URL that is sharing local database files with another connection URL.

Background:
The JDBC driver uses a local embedded database to automatically map Salesforce objects and fields to tables and columns the first time it connects to a Salesforce instance. In default, it uses the user ID specified for the connection as the name of the local embedded database, otherwise you can set in your connection URL the DatabaseName.

Workaround:

1. Go to the directory where the embedded database files are saved, <ODI_HOME>\odi\studio\bin and delete these files: odi.app, odi_properties.new, odi_script.new, odi_SFORCE.map, odiSFORCE.native, odi.config (The files names could be different if in your config url you have set the DatabaseName. Default database name is the user id which in my case is odi. This files will be recreated when you create, update or open the Physical schema for this connection.)
2. In Topology > Salesforce.com > [Your Salesforce Data Server], update the JDBC URL to jdbc:weblogic:sforce://<Your_Salesforce_Instance_URL>;CreateDB=forceNew;ConfigOptions=(auditcolumns=all;MapSystemColumnNames=0;CustomSuffix=include;);
3. Save & Test the Connection.
4. Open the Physical Schema so that the embedded database files will be updated or recreated.
5. Disconnect from the current ODI session.
6. Reconnect.
7. Update the JDBC URL. Remove the CreateDB option. Otherwise, you may get an error that the Database is in use. Save, and your connection to Salesforce should be good.

For more information on the connection properties supported by the Salesforce.com driver, see http://media.datadirect.com/download/docs/jdbc/alljdbc/help.html#page/jdbcconnect%2FConnection_Properties_11.html%23wwID0EZT5Y.