SharePoint - External Content Types on Oracle Database
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.
sharepoint sharepoint-2013 bcs
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
add a comment |
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.
sharepoint sharepoint-2013 bcs
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
add a comment |
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.
sharepoint sharepoint-2013 bcs
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
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.
sharepoint sharepoint-2013 bcs
sharepoint sharepoint-2013 bcs
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
asked Dec 27 '18 at 15:43
Ctznkane525
5,8232933
5,8232933
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947503%2fsharepoint-external-content-types-on-oracle-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
add a comment |
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.
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
add a comment |
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.
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
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.
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
answered Dec 27 '18 at 16:08
Ctznkane525
5,8232933
5,8232933
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947503%2fsharepoint-external-content-types-on-oracle-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
