I have an Oracle database which I'm trying to connect to via BCS.
There's a SQL Server as a passthrough which view to the Oracle data.
The PK key in Oracle for the table is NUMBER(7).
When selecting the view from SQL Server, I can hit a single record very quickly . However, when attempting to create the Read Item Operation through SPD, it is defaulting the PK to a decimal. As a result, SP when performing the Read Item Operation is converting it to this call which I found in the Profiler:
exec sp_executesql N'SELECT [RECORD_CODE] , [PAGETITLE] , [PROJECT_CODE] , [CONTENT_DATA] FROM [dbo].[CONTENT_MAN_CONTENT_MAIN] WHERE [RECORD_CODE] = @RECORD_CODE',N'@RECORD_CODE decimal(5,0)',@RECORD_CODE=19258
This conversion to a decimal causes a full scan of the data, which means the Read Operation screen is very slow.
So, why questions are: 1. Is it possible to change the data type in the external content type to an integer? 2. If not, what other options do I have?
I cannot change the table structure and would rather not build a service if BCS can be used.
I changed the process to use a stored procedure instead of a straight select, so I now have more control over the casting of the data types.
I'm now able to get a single record and hit the PK directly from Oracle.