SharePoint 2013 External List from Oracle database
Brief Description
This article describes a no-code solution on how to create an external content type from Oracle. The article will briefly describe the existing table in Oracle 11gR2, the SQL Linked connection and then creating the Business Connectivity Service in SharePoint 2013 on-premises.
Setting the scene
Below is the setup and configuration of SharePoint, SQL and Oracle
- SharePoint 2013 with SP1 on a Windows 2012 server.
- SQL 2012 with SP1 on a Windows 2012 R2 server.
- Oracle 11g R2 on a Windows 2012 R2 server.
There is a custom table on Oracle's 'Home' database and this table needs to be presented on SharePoint as an external list.
Pre-Requisites
Oracle Server
- Oracle listener service should be configured and turned on. You also need to know the what the protocol and port number is. Below are screenshots of the service, the protocol and port number.
https://84nsgw.dm2301.livefilestore.com/y2pskysaTzNAvFQQUwoDYNk4Rhsk7llLCkkZbau6C1v1c1lv_R6X1uk3EwkCxXNZ_kAV03rijQ7SVOc5WoAiSAetWEEtYQK3llSi8J2-TJjPrx23Gp-JSVcr0Yv0nsTt9TYhKGw8hFXuw1McyBMvDoSermim3fznMEnD4U1D8SaGL8/Capture2.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pZPw9yCUGU6XVltZ_uAOW7o3usl6LH_PHcDTrxI1bPess-HNi5oXGiR5Pd7WGHr9hp9U3qpvjDlCP7pN5eiU3d7l4QRIFcZ6W9IAGovRW_vG8vsOJEMEU67js3FXX6lbK3aZO649dPMDgGnvtX5rdIdPdjYBxnLBFuSsfvRDANRY/Capture3.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2peI_do5_0meomxPjTBypF_MftTZ4IMy3HQh6K8ldUq4DW8-5i9G5feH83etptpwAl4dseqx9V7Pdsq05-BCSx1zrU2ypchw-NW6q0W7nQX9soW-3VuR9byk6t8uh-XTuAGnYnP0Ey2UulHx8D5j_QrJ-vdqEKjc1Y6L5ZEKYcqBM/Capture.JPG?psid=1
SQL Server
Download and install the Oracle 11g R2 client for both 64 and 32 bit. The 64 bit is to configure the ODBC connection which the 32 bit is to use the Oracle SQL Developer application to create and confirm the connection to the Oracle table.
Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64)
Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)Create a database and view on SQL server.
Have a service account (SA) that has access to the table on the Oracle Server.
SharePoint Server
- A Secure Store Service ID with a SA that has access to the database on the SQL server.
- SharePoint Designer 2013 to create an external list
A video has been created to walk through the setup of the current environment and to show the pre-requisites.
Setup and Configuration
This section will primarily cover creating the new Linked Server to the Oracle database on SQL, creating a new database with a custom view from the Linked Server and finally creating the external list.
SQL Server
Setup the TNSName and the SQLNet files and add environment variables
In order to create a successful linked server on SQL you will need to have the TNSname and SQLNet files updated or in some cases added or built. We will also check and confirm if the registry settings is pointing to the correct ODBC client, add the environment variables and finally create the ODBC connection.
TNS and SQLNet
It is best practice to first open your command prompt and run tnsping <Oracle Server name> to see which client is being used. In this scenario we have both 32 and 64 bit Oracle client installed hence after running tnsping oracle11gr2, below is the result.
https://84nsgw.dm2301.livefilestore.com/y2p1L-EMcmOq8Jk5MRhsvjyFn8wLnompkj-FsLxuIdi59L5Gy2xxv1PePC9fU9tw12OVux9az4-NfIt9UD9sdIsvPvux9d2APM_aVpTtevKpPalDHmGgQ_IzRzCVtjwLcCtdXHhdOTtHAY3RikeBO-LvTzQvjtGWstcfJbHObJYKg0/Capture4.JPG?psid=1
The above screenshot shows several important settings. We see that the location of the SQLNet.ora file's location, a 64-bit TNS ping utility is being used, we are currently using the client 2 oracle client and that the connection was OK i.e. successful.
Go to the SQLNet.ora file location which is show above. In this case the file location is
C:\app\administrator\product\11.2.0\client_1\network\admin and below is what I have.
https://84nsgw.dm2301.livefilestore.com/y2pR8papkMy5WsH4ypw77ek7CwLLK30DBQMfm4F3-cWG8gg4yVnpzHTDd8QMgN33NzK9DprIsMCM4fe0IUbbmM-gBFajrN2tmVFklFstwI9pY9XNrjG11iJPqJUPt-JrU1eaxUHMtTDVFBMak1qTuQdCSKbkFLL1jvejSILdAMp-mk/Capture5.JPG?psid=1
Below is a screenshot of the SQLNet.ora file
https://84nsgw.dm2301.livefilestore.com/y2pS5j26Rf6IQfn25BJA1EgCZrRFWFMNbQQTpswMUiBtldhe-LefgLIRE06vvMmNXrlWqKKbPxltFWGAxmbro0OVIjPcmWHAf3SdT1Q6iTGMftj4T_I7NHTfxHwziF-LCL1uS2vFCXs9KVl974T9NT8dMtTQVIG6DOpqDecoQjpoGI/Capture6.JPG?psid=1
Next step is to create the TNSNAMES.ORA file. Open notepad and copy and paste the below text in it. DEPENDING ON YOUR ENVIRONMENT, THE HOST AND PORT number might change. In this scenario, books.emp is used to match the Oracle database and table name. The host and port number is what is setup in Oracle server as show above. The service name doesn't have to match the Oracle database hence you can assign something that makes sense to you.
books.emp=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=oracle11gr2.christianfamily.biz)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=books)
)
)
Next step is to create the registry entry in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. Right-click on the Oracle and add a new
String value. Below is a screenshot of the registry entry where we are adding the location of the TNSNAMES.ORA file
https://84nsgw.dm2301.livefilestore.com/y2pCwIHgeP-lb1gIEOBh_F41ZNiA1d96yzxgp3F4nYl2hbVkWcE_GcN3ZJOBWnx0M7Mzyl1-0Lfifh4i0ISWert3opLU1wM-86vGRuRVXsJy0uT8uFfgaa8oLU0fYPSwY048PvPVJat-BRKUV0YgtN56AfSIhhdHkNde6vF9Ju1yw4/Capture7.JPG?psid=1
Next step is to add the environment variables. In Windows 2012 server, go to Server Manager>Local Server, click on the computer name, in system properties click on the advanced tab. Below is as screenshot
https://84nsgw.dm2301.livefilestore.com/y2phaNTiLhNf1Q79ngE1gF5gyuj9M0j6c3qHFTsisElbaCOevZZem0XbjNZn3y2mJQz691rXeXiwmo6jc1VrTh2SZl14lzyeFnEaS4CVUbdGvueAUqeCyngCLHvXl-Tb0a3zBT_boJ6_SXJEVPjMIM_XjGJBY97qsR639jfhgZBoXQ/Capture8.JPG?psid=1
Add the following system variable
Variable name = TNS_ADMIN
Value = C:\app\administrator\product\11.2.0\client_2\network\admin
Below is a screenshot
https://84nsgw.dm2301.livefilestore.com/y2p_YhwtR7hOrowyraA6dOoa_aVP_E3I_zHcb1scDHky6wCeiNanz6K7EYruJwfiXFO7tnK-vNZbYtXJw6rYIoXVUFzZKbiM7_EI8aOHbi9m3_yqlOLLRPuybjlhSSl_fQ93MdlTeWrvhBNhkIA2IHw_JJko9fcUs_3qLvtAO9QAQs/Capture9.JPG?psid=1
Now, let's do a quick test to confirm the settings made thus far are correct by opening the command prompt and entering tnsping books.emp. This is the entry added in the TNSNAMES.ORG file. Below is a screenshot of the tnsping which shows it was OK i.e. successful
https://84nsgw.dm2301.livefilestore.com/y2pEia4N3mqJautR1wqOaDmqvHm8gC-lnHOqrU_JAoz2do70OJb-b6SCwxJwyoHFWlHazmynO9OxjIVlOSEKHOlPsouAY4gsC-tqLC575YrYFCAMyLfEHWTLaAPxv1mwiRaHuhFqmmAn8AvMCzbRdLLF6wvupQtD-aeOCCPM-rqEek/Capture10.JPG?psid=1
Create the connection on Oracle SQL developer
Open Oracle SQL Developer and right-click on connections and select new connection.
https://84nsgw.dm2301.livefilestore.com/y2pMVqnFJr9Qgt7SJemlP3AiTeE1SO6-AelUuxp7jca81mb5opu7UzFza1Iu52LYinoDhojaFfPKkHjwwsSfACkYC9YUsuRQULrCeY-QpfjmkrAmNM6FhDSDSeljshQ_2ma5dmCSyYgg8YvmJBD5hTXXpLgy4ZciqTZI6vEGHXbOVA/Capture11.JPG?psid=1
Below are screenshots of the settings added for this data connection. The connection name can be anything that makes sense to you. Select TNS as the connection type after which in network alias you can select books.emp which is the TNS service we added before.
https://84nsgw.dm2301.livefilestore.com/y2pYz6a2pcTSebsZGE6QIqMf8XHOkB9ETHOTKqPIGajl69OzooydC_68K4m7YwxY05_ZKpF9e1w1q99Pcme2a29WuCWlFQBX0HZ3RlrKOVITCV42jQK7HIl6ju_aqhOHpw4sEEgrJPcOy3sV051Cs7tWkKjCTN0Z99gqge7a0mA-ks/Capture12.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2p2_aQ5spw3tyZyQXLREkjvaDg9htdvIzjlNafYmDmjG5TsaufJeabMDf5NsIJNC2nRFnhvo-a8ZdbqV-jpg74nP77eCpSYEnGhKC01qAq2XAVqdhrTuVvaVrrYq__ymWtfdSw83phmahTVHPqHblSq7Q4x0IVZmy26Fsja4KjCQI/Capture13.JPG?psid=1
Also, in this scenario the system account has been used, however, you can use any accounts that have sufficient access to the Oracle database.
Click on the Test button and confirm it was successful.
https://84nsgw.dm2301.livefilestore.com/y2pfeKxFg7K5VmCp6nwWux8sLXbLg-SEdzq9-PFSN45dlgt3F2hjeW0r3T1N8_QWgDBRHqH9ZVQMikFyWwV6EdNRUwVjdYdrvj1ozhfG4Tg5ANtFJbp4u-EcnapVzuNjmpAPGXMvoxebvsCcB2h37Yn5PpHy9iTrpVr-noyrNh5CEM/Capture14.JPG?psid=1
Next click connect after which you should see your database and the table. Below is a screenshot of the Oracle database and table in this scenario
https://84nsgw.dm2301.livefilestore.com/y2pvOx9PsKY24gKDEwt9zJ39HPZg76-20TF5vpIXSYGxRzFyxKviORgIjFQnJAY7fmhKgagvlZxEq1Zj-Oi6Ed3nxYF8VBh9kZaGtsWyA71wjypFBFeYp0ui4M6kSEhdKLtVEf9EnMC-Cy6XLiSLxiK4jhqRDzUA4TZknKOvN3k1fI/Capture15.JPG?psid=1
Create the linked server
This section covers the steps to add the oracle database as the linked server to your SQL database.
Open SQL Server Management Studio (SSMS) and connect to your SQL database instance. Go to Server Objects>Linked Servers>Providers and confirm that you have OraOLEDB.ORACLE available there. Below is a screenshot
https://84nsgw.dm2301.livefilestore.com/y2pAK_KSjZecSxHAluH8XFq9jyZ5xS6HixzJz6ve0DM0kdZJhgxQgObyjqsbB55G-g7teOT-LlgzTsTuq5aJ5znZC4hTJP0Df4dS9R5-EjNGTJj8wi58EbjSANoTQX7kDnzQBDR-t82hmB82KmsisUzrdaJODLRbCbcKFUVnTjfwkc/Capture16.JPG?psid=1
Right-click on OraOLEDB.Oracle and choose Properties. Enable Dynamic parameter and Allow inprocess. Below is a screenshot.
https://84nsgw.dm2301.livefilestore.com/y2p6gdEpobhWAYxyn0Wi8rVfkavDm3wdVXi5ppOV14yVWVdhMgtUsiP7Xy6pUzVVDGFJKzfyAdiItJcTfoyuxYl0eHf72P1vf2VIzjw-zNYu1GuPRdPryNdXRhdoR1n-ZxUwONJu3fcuQgQSDtVFfdxroAAAlCDf688kD6uSWu5qrc/Capture16a.JPG?psid=1
Right-click on linked server and select New Linked Server…
Below is a screenshot of the General settings. You can give the linked server a name that makes sense to you in this case I used ORACLEBOOKS.
https://84nsgw.dm2301.livefilestore.com/y2pEIHRXRFD0GNZJCMHLIV1XitIMEv3ZQtzFpf-KTkhlF_Ne0lgikDPOdLmFgPM42LKOAfOox8mv0_NNAcOLu1-4t_MIkesj3ii0VEO7C8E9-UgAtfwqckqDpey1smxg_y3duGE8AgytM9vXYfS7qJQZjv-aJP6pD3PQqwi3lRbguA/Capture17.JPG?psid=1
Below is as screenshot of the Security settings. We are using the same service account used in Oracle SQL developer.
https://84nsgw.dm2301.livefilestore.com/y2pxruQsiy9VQZAe1roTnpC8L66oD0iZLIC6S-nD-lg9N7_guBOG8wTKSacmm9E3b-9KKYfHFm9MlByWgISmIsurmReXngY9bWwAFoLuFTKOxio6Xkq_8xpP6ALneoylMRA1x1cd3FoezFAmNhObdev2-9lLkWvMP2tiVccv6WSclg/Capture18.JPG?psid=1
Click Ok and confirm that your linked server has been setup. Once it has then go to the server and confirm that you can see the tables and views. Below is a screenshot.
https://84nsgw.dm2301.livefilestore.com/y2p1DGx1Hdl-vvS7TZD9xB28RMFAxZT_EtmbiOgy2TAZMDphpXu_YXgKUz9vo-Jo8r4OsB8aD9PTmxWPtCbyzZbWQ3UZl2IoD77QEZJw9bfs_IlbztTeP75AKABRBHqeg7fKnprLNohsaqzykwTqB4oAgoK3GejdXhQSm2G8cUD-Gc/Capture19a.JPG?psid=1
Create a new database and create a view
SharePoint Designer 2010 or 2013 does not give us the option to pick linked servers from SQL, however, there is a workaround. Create a new database and in it create views to pull data from the linked server. In this scenario we have created a new database called OracleBooks. Below is a screenshot
https://84nsgw.dm2301.livefilestore.com/y2ptHtouIlQfxFd6X1PGULXq4aqCBBqb5pt4UlDaDQK5HgIeaucO8qOgAyuv5YIq7eJhX6CxkogeHD2rr3xtZeLMV3HentoxwbmSWbxCl3UxruOXEtHJVlA_Oas9fFsoVcs7ollcn7TUChnwHqgCrBu4BwQrduw0Nzj2SHKgh5Gb3g/Capture20.JPG?psid=1
Now, go back to the HR.EMPLOYEES table in the OracleBook linked server, right-click on it. Go to Script Table as > SELECT To> New Query Editor Window. Below is a screenshot
https://84nsgw.dm2301.livefilestore.com/y2pgKFAcuPfxpI2gK4ogwY4uE7XSx-M894_XQ89_iwgaola0hbJyvPexfPPHOy2lNj7BPQ6MG5r6aeWkqeh3OIGR3SGlglXUNDzAK2r5BLL3CQCvW8y37AnOvM3ntvJX6-ZIriAOKBBTk4pwcHsaZxD_swMdI_8lJSR3SNzwIBsO6w/Capture21.JPG?psid=1
This will create the query for us as shown below.
https://84nsgw.dm2301.livefilestore.com/y2prIEe2MAubGCmOhftZNhBmGkvte6VIbEsg4QfxLuaYh1queeu6eF8VM-mHU-s4EX2iRUsbejaMX1iW7ia8YbPQE-d8LPeatkunsYkgZNWGMtstWtt_GcGmZSN5l17M0MUIcjg-QXwsOJ_HHCNhQskJfHHfHBT5BzkQpHCh8p4_Tw/Capture22.JPG?psid=1
You can delete any unwanted columns and then copy the query.
Now go back to the OracleBooks database that we just created, right-click on Views and select New View
https://84nsgw.dm2301.livefilestore.com/y2pyWxLGo2ZMYrJ-dcMsIr8Zg0fW1MXqEC40hB59M8qoKi-VlAN0kRezZrCfhQuqtXPrYhtZ8uqui9cNFSAe4AV7SStEQAFEUlPl7aoZlD0ror7i92s8G4Jh4-XiNM8c2WLnRCasYqpKnvRYI1UJAE4iMsOGh-xGV3LyG6Fa_oN58I/Capture23.JPG?psid=1
You will receive an Add Table window as shown below, click Close
https://84nsgw.dm2301.livefilestore.com/y2p5XK1Xr1eZWhIzge5LgeprZuuaNvnOXulDpc7eBiQy0uR_a3DUm5GnorgMeHFXyi-QdcEy87MOURT9FcC9sK0q57FELifAA4skqB2f62lyJbYcu9Xk8f5N4fe8s_cEd8hiRDI_bIwcpQfTmp-s1dRQyeC6Sid6W-L7DTFq0x6mGk/Capture24.JPG?psid=1
Now paste the query that was copied a moment ago as shown below
https://84nsgw.dm2301.livefilestore.com/y2p1k1y7sxwCmMdAcdJ9iRP-oe4pUBe7c1Vhpla-mS6klQCnBYn8s0x0rNLduHfdh6tVKKFLPMJmCuGDGT_zUEvMVZGNtYDxmX5q8DG3iH1QQR-pz5-8lMawmQ4sBHS-d4TvA2AVpcnGvl7UtdX7rlMK3urWs-xf0eqWlQyUuQdb3k/Capture25.JPG?psid=1
Execute the query and confirm that you have received data from the Linked Server. Below is a screenshot
https://84nsgw.dm2301.livefilestore.com/y2pXyl8KAmWkbZazNMUDUdfY_GLhIEqPY-0VVQcPYfcnwOHNyMiiriNdvvQMCPfi0jM8K9mXj8bmsXambpFa53QFOaZuwVEXHb3OCUWqzSPamYOxKIEGD4HEDFXAgneVetNH88xeC05_nqy-Nsfswe_o4NV1M96WK27Yxd7hQD08-k/Capture26.JPG?psid=1
Give this view a name and save it.
A video has been created to walk you through the SQL configuration process
SharePoint Server
This section involves adding a new secure store service (SSS) ID and then creating an external list using SharePoint Designer 2013
Create a Secure Store Service ID
Go to SharePoint Central Administration>Application Management>Manage Service Applications and click on Secure Store Service Application
https://84nsgw.dm2301.livefilestore.com/y2pmbrhl2jq-br4eiK8D6agrigxN_f6DaV3KsugH2zf-6s1rkj6JWd6aIOt6-G4rV6cWKAGdxGuon8gOSthm0Xw-N4oW4ZFeUnnxSAySsS1s7n8A1W6OTImV2z5C5JeC5ykO8zo-X6q_MkFZw9fDRQxGrAWjQ7bZVHL4qXhlTlrC-A/Capture27.JPG?psid=1
Below are screenshots of the new ID
https://84nsgw.dm2301.livefilestore.com/y2pVHSTc5W2RChfNzknvHJZCqbc1ba21ZAExS_qZPBLvNSjfWib_pR2Rgi_nR642rU64VWO90NznC67vsDY0A5hSK_TEFIRSGAEV9jeRmTPRmHzX8rE98jy4z7tydz1zsuZFHps6l2bu8_EIcet1T-mgQ6Uc32hbxqNVW-mQ7mszl8/Capture28.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pzo2fSB-KR_PR7Vlui73GsopvAb-dtVIFhK9lgOlfWrlPnHH1Ft7Ch6ghjZ91ssl3ZOMc04w5Q0VDEU1ceM85RhvNkUuQpraGwCP7OYMQheWgepyEh2vs-HNO555GbAJfeMmQK0XjHvef_-WXIX3OVEX_QKnv-FeWWxP8Cf0-_xQ/Capture28a.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pCz5O2dxv6XpP_TSnn5tdTlZQMrQwj5NH3S104RnjGztNSa0FnwPVIjPu-8QvtT_xeb78H0gBtpaYtMdtws5uIdesl_cDLU4fj6-sQ0N1QjfZcBkENn70mGknPZ_D0jWV8-O0XhPtqT4Ib5c9xDUChthlRoV3On2gmkt879SXITk/Capture28b.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pmXmd2b5dkXBn4g3qb4Amky8uBf0qrB-2y7gq1rFLgiWFgub8fRCS06DfJQAzwwH9OIVwv0yoeo31f9w4lhTPMLgK3UqWOsCvKJ2lZQn9H_Dmb5Ou8lOIcWo-o4z-bl5kqgh2jwUPhD_zPrMPSE-J0zQx7yaMx2DlrgmJnhZaol0/Capture1.JPG?psid=1
As shown above, ora_admin is the service account for the SSS ID hence go to the SQL server and give this account access to the OracleBooks database.
https://84nsgw.dm2301.livefilestore.com/y2p1Sb3338ZWFXvw3blsQLyBvMnXtRNSqZXfNV3nmkgwF4Xk5wE5mvwc6Bp8KGtAwO4QBgd6HYxJwWHRDbl71AJHHdX8P2pcihsK7VVe4dqaMg-rWYG4Uhy_jS7y1DswDZeZRm9aoXdPqf-hJvDysM239qE4wh0sYxv6zBxN9JxIZM/Capture29.JPG?psid=1
Create an external list on SharePoint Designer
Finally, go to SharePoint Designer>External Content Types and click on External Content Type
https://84nsgw.dm2301.livefilestore.com/y2pnYHQJN51IMX0-shvENYms6AsAZMJd39fqOdi5CyPyjnGBDE3nPkZ8s_XauUP-QaQCcYUyH-a0VL5Ekcw512ZtLkHPMyKmMkpSMy1HT0u7qT9uUUK5F5UeDsdsdllaL5BFeTULkc1dLx2I1Xmy5o8GyR9V7moRUl3rEf_D3623q4/Capture30.JPG?psid=1
Give the content type a name that makes sense, in this scenario the name is OracleBooks. Next, click on the link for External Systems as shown below
https://84nsgw.dm2301.livefilestore.com/y2pulTLwJzvZXwYysl3R62wINM12SmI29h4khETgdUmmGlUuRHg4fVcoepdmFflJwV_JvXhnuyGN0TcYFIAdsYSE4VY9jBHHD_g-rK7exGWq17e_i_iRsO-2IixdmCDjIUTasY7bJB3g1sGDOamxBORVGZkPMCmjiYca0DY4Kn9dfA/Capture31.JPG?psid=1
Click on the Add Connection button, and then select SQL Server as the data source type
https://84nsgw.dm2301.livefilestore.com/y2pxVsve3IXclv7Rjws6Hdoxfk_uVzQWQ8YbhwxpVpbkp3HY_4mzNTjz91amLBCciiru-gxxhQ-ewHt6V7Gmcyyz4ZN0YHKlxm_tHr2QDHPBQAhvZuRuwtlYelejSW3YzJGBZsEK_EZTRb8d4wa-H_DyY-84jwhtIks0idHplXDzlE/Capture32.JPG?psid=1
You can now see the connection made to the OracleBooks database as shown below
https://84nsgw.dm2301.livefilestore.com/y2pA4hXOky2sCdaEvK7yTZ8R7Ve2yUzDwA8KKd1O0C3LCCJrMWjpjUoVHtJR5BEYDsaTI3kY9p24RH9IZ6cWAZKTum_2Wi5-6vNG1RetHhIImDlgsSssTOIHW3HfgaUoNFRKUPs5CBDBYRNV6F66YmNlbSpte9GTzudWk2sOBf_lMo/Capture33.JPG?psid=1
Right-click on the OracleBooks view and select Create all Operations
https://84nsgw.dm2301.livefilestore.com/y2p5MVKiK_ezLD1NxvKNsFvMhMLp3CG-mnx0vk19UonD2WfyOhaDlJlH_rQKK424ntHpQzhkLvqY9LFi5DZ4b425m1f1v-fsmSYA2vEp9cgC0fmm8kfVv3wIIOInWo8jWwm6TS7Edy8VQBZxh2cpOz_8G49ftbeB1qo9KU-g-cYe8U/Capture34.JPG?psid=1
Follow the screenshots as shown below
https://84nsgw.dm2301.livefilestore.com/y2pEiP0b7Qv17hE-itpyoHb0qF-6eHhqoFmoCrFyRFljFez1yum-XNuvCk99v9o1wQOvoG5fKoBGyvLqpGMcoAkUiKbGBXoISHwhWHxp9d0t5Sdh5j3cmNDw-J6bUdwAyV3sm0NgstHI-I4Z9ZmT6i5e06n-Y2UexVXud9xqbCWdo0/Capture35a.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pjHLyVA_O9-mXiHcpFlp964Bn5_u-OCsFZSr69ZY526npKCh-Zj-0FPng6JaFUxSJ5SChBqEtcWnY6JMCSKfT3FnqIrp02J1g-_eZkPuoHzYtoOshSkiIvK2YpFUGA7RIB_txu2h4Arc5tDZiS6E6_wEEDM_eyfQNToQeHqhAzUc/Capture35b.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pAu85TopRnYUmHhPm6m38vU9j5CF9MY-w3usjzRhx1AfK5HnoxlDW-LLrGMNTtkeuglpdbMDMTVJ_XoWPUk7quuuQPOFABF29pw9hHRq7rfoGdOk3B0GqjPQHV1mwTwxl32vsjRK97qRJQ3Z0-GmPCWjsTVLBwMYmlOZMxj3iCDw/Capture35c.JPG?psid=1
Make sure you save this connection.
Now you can go to any of your SharePoint site and select the External List app, give it a name and select the external content type that was just created.
https://84nsgw.dm2301.livefilestore.com/y2pV1lEuOftVgCtaEFdz77lUyBOtqI9qybp6ln4RzUo8dEbeopx7AcUI8YOok4hgDfSenDkLxH_BKC2cOVqw8XLv49H2VCnGJK8bQMSOaYaNKShdSn4ZmzW_JVujvoMRKmvdpPphrBTrpJUUKaYGwbCUfU9fEk7IuT6K0PYpMFXWG4/Capture36.JPG?psid=1
https://84nsgw.dm2301.livefilestore.com/y2pwp_iSLGTppvoyTFf0unx6KVkv3CzWhlYCw90XLw09n2TtDNfpigkOEuPIF61JPQRIJVXllmzeM2-3h5hPpJXANoHm0ZOdwy1H7XVsB07o_YNUsOdqghH7cjbFDVuBqrgFhyMKW2OqzJtQQjLCX9q2uHHnUIlrCcNSoDFynnYH_s/Capture37.JPG?psid=1
And finally you get to view the external list!
https://84nsgw.dm2301.livefilestore.com/y2pQN8BAh9Pp4wong1RqydxAp6xP76PHaey3jH0gEJKAUQuLJXSwBqG140qJQLxKKNh_m0okMccegkHl8mM7wWVppyJP3nr4opdiWutez_VEVNSHvBejxWLvqDnpTxLmwry2DaekfUwhsd7dck1FQk7pRiIQsqKYk8ye1EcYx7qkdk/Capture38.JPG?psid=1
A video has been created to walk you through the SharePoint configuration process.
Issues and fixes
Depending on how your Windows server operating system is configured, you can run into issues. Below are few potential issues and their solutions
Issue #1
The below screenshot is a common issue that is encountered when the tnsnames.ora file is either not setup correctly or if incorrect information is added when creating the new linked server.
https://84nsgw.dm2301.livefilestore.com/y2pB__lPEdOkfUk3mPfca9oeadBFpsTG0mbh1BO3tLcgWQTPKcP4YOmVJFvpnH0u8_yzARGjsR_Sl_WfrDNxAlxtu4a0qvce4uU5oQdEph8OytD2p2gjz2Hx38hT44STBrz7EzM1bGPMkg6tJvdOCTn13mscCLFe7Ezg-MWyXU4MmY/Capture40.JPG?psid=1
Solutions
- Run the tnsping <tnsname> command and see the result you get. If you get the below error TNS-3505: Failed to resolve name then check to see if you are using the correct Oracle ODBC client. The client location used is also provided in the command prompt below. It is possible that you are using a different client where the TNSNames.ora file doesn't exist. You might have to change the location in the registry and environment variable as well.
https://84nsgw.dm2301.livefilestore.com/y2pVWm8Ce-3EVeI6gh3VR6BZ3uakkAl5Z6GKBWpz51NBWEp1j1jSkDYrXZxvBD34ExibVBLuNqRr-giaLuhXgOQPWtehiC0oeXylVIGqFtlMLSSOKgLMHfU8qx_HKNdYfGhG-PA68dp1s4QVqHOtIRo-gxPS70fr_f-yuUvql0yiuk/Capture41.JPG?psid=1
2) Use the correct tnsname. In this article, the correct tnsname is books.emp AND NOT books.
Issue # 2
This issue occur due setup and configuration of the operating system and hence some changes are need to give service accounts the access they need.
https://84nsgw.dm2301.livefilestore.com/y2pB__lPEdOkfUk3mPfca9oeadBFpsTG0mbh1BO3tLcgWQTPKcP4YOmVJFvpnH0u8_yzARGjsR_Sl_WfrDNxAlxtu4a0qvce4uU5oQdEph8OytD2p2gjz2Hx38hT44STBrz7EzM1bGPMkg6tJvdOCTn13mscCLFe7Ezg-MWyXU4MmY/Capture40.JPG?psid=1
Solutions
Refer to the 'Permissions needed to setup linked server with out-of-process provider' article. YOU WILL NEED TO RESTART THE SQL SERVER FOR THE CHANGES TO TAKE AFFECT.
Depending on the location of your Oracle server, you may need to extend the connection expiration time hence in the sqlnet.ora file add the following right at the beginning.
AUTOMATIC_IPC = ON
SQLNET.EXPIRE_TIME = 10
Conclusion
The steps provided in this article work for Windows 2008 R2 and SQL server 2008 SP1 as well. This no code solution provides a step-by-step procedure for Oracle data to be presented on both SharePoint 2010 and 2013.