SharePoint - External Content Types on Oracle Database












0














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.










share|improve this question



























    0














    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.










    share|improve this question

























      0












      0








      0







      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.










      share|improve this question













      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Dec 27 '18 at 15:43









      Ctznkane525

      5,8232933




      5,8232933
























          1 Answer
          1






          active

          oldest

          votes


















          0














          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.






          share|improve this answer





















            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
            });


            }
            });














            draft saved

            draft discarded


















            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









            0














            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.






            share|improve this answer


























              0














              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.






              share|improve this answer
























                0












                0








                0






                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.






                share|improve this answer












                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.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Dec 27 '18 at 16:08









                Ctznkane525

                5,8232933




                5,8232933






























                    draft saved

                    draft discarded




















































                    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.




                    draft saved


                    draft discarded














                    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





















































                    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







                    Popular posts from this blog

                    Monofisismo

                    Angular Downloading a file using contenturl with Basic Authentication

                    Olmecas