We are working on a Proof of Concept using Salesforce Lightning Connect along with Progress DataDirect software to connect Salesforce to our Oracle E-business Suite ERP system. We are facing an issue updating or deleting records from the oracle database when the primary key is a numeric field. Error message received is "Type mismatch while updating the external column ExternalId"
Anybody else faced this issue? This works fine if the oracle table's primary key is a character field. One thing I have noticed is that if the oracle primary key value is 123, the value for the salesforce external id in the external object is displayed as 123.0
I have also had no success reproducing the error myself, but I have been able to reproduce the "123.0" behavior in Salesforce - instead of the "123" ExternalID.
-When the Primary Key is defined as "number", without any size associated to it, it will display in Salesforce as "1.0" but will display as "1" in DataDirect Cloud SQL Testing.
-When the Primary Key is deinfed as "number(X)" where X is an integer such as 25, it will display in Salesforce as "1".
With both of these tests I was still able to update and delete records regardless of the Primary Key definition as number or number(25).
Thank you for the explanation!
That explains the issue however we are using an Oracle ERP environment and we do not have the option to re-define some of the tables defined by oracle.
I would like to follow-up and clarify the repro for this issue.
Step 1. Create an Oracle Table with a Number primary key that does not have precision
create table employee(
constraint pk_employee primary key (employee_id))
insert into employee
values (1, 'Idaliz', 'Baez')
Step 2. Connect to this table with DataDirect Cloud and execute a 'Select * from employee' --- this will result with a display that looks like:
EMPLOYEE_ID FIRST_NAME LAST_NAME
1 Idaliz Baez
Step 3. Expose this table via OData in DataDirect Cloud (copy the OData URI)
Step 4. In Salesforce create an External Data Source using Salesforce Connect that utilizes the OData URI and ensure that it allows Create, Update, Edit, Delete
Step 5. Create a tab with the External Object (the exposed employee table) and view the Oracle employee data within Salesforce. It will be displayed in Salesforce as
External ID EMPLOYEE_ID FIRST_NAME LAST_NAME
1.0 1.0000000 Idaliz Baez
^With this repro, I was still able to edit, update or delete the First_Name or Last_Name and did not see the same Type Mismatch error that the prospect received. The prospect did verify that changing the table definition from Number to Number(38) made the above output display without decimals and then they were able to successfully update the fields.