Share via

SharePoint 2013:BCS Connect To Oracle DB No Code

  • Article

There are many posts describes how to connect to Oracle DB database using .NET assembly Type of BCS, it requires writing codes and install the Oracle Client or OLEDB on every App and WFE server.

I was going through the same road when it hits me , I can use Database Link to connect to the remote oracle table by installing OLEDB client on the SQL server only. Afterwards, I can easily create the BCS model by using SharePoint Designer.

To create Database link use the below link

http://blogs.msdn.com/b/dbrowne/archive/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server.aspx

  1. After creating the database link create a view to use it when creating the BCS let's name it MyTestView

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F3.bp.blogspot.com%2F-3RM5ah1pksA%2FVElBDASr1TI%2FAAAAAAAAAkw%2FI0HWJIW2eYE%2Fs1600%2F1.png&container=blogger&gadget=a&rewriteMime=image%2F*

  2. Open SharePoint Designers and connect to your site collection

  3. Click on create new External content type

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F2.bp.blogspot.com%2F-k_r3FbrLXzI%2FVElBDu8lOPI%2FAAAAAAAAAlQ%2FdROj_WW-apo%2Fs1600%2F2.png&container=blogger&gadget=a&rewriteMime=image%2F*

  4. Name the external content type and click on the "Click here to discover external data sources and define operations" link

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F3.bp.blogspot.com%2F-VLOkpCAj1ow%2FVElBD-TRS-I%2FAAAAAAAAAk4%2Fx5v1_bBdiqg%2Fs1600%2F3.png&container=blogger&gadget=a&rewriteMime=image%2F*

  5. Click on Add connection then Choose SQL server and click ok

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F2.bp.blogspot.com%2F-MzyrydqDHM0%2FVElBEPJgQMI%2FAAAAAAAAAk8%2Fez-5Sm_EKs4%2Fs1600%2F4.png&container=blogger&gadget=a&rewriteMime=image%2F*

  6. Enter the server details and database name

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F2.bp.blogspot.com%2F-NpwW2sd4hjM%2FVElBFAKFjBI%2FAAAAAAAAAlg%2Fb8DA-NqzsuQ%2Fs1600%2F5.png&container=blogger&gadget=a&rewriteMime=image%2F*

  7. Browse to the views and expand them you will see the view which we created (retrieve records from remote oracle DB)

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F1.bp.blogspot.com%2F-GWw2nfv5pFc%2FVElBFNDUnAI%2FAAAAAAAAAlI%2F_b7cx9CwDWw%2Fs1600%2F6.png&container=blogger&gadget=a&rewriteMime=image%2F*

  8. right click the view and generate both ReadItem and ReadList operations

  9. Make sure to map the identifier in both operations

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F3.bp.blogspot.com%2F-O_jRoZo0tFY%2FVElBFqQ160I%2FAAAAAAAAAlY%2FhJWcxzXvcEY%2Fs1600%2F7.png&container=blogger&gadget=a&rewriteMime=image%2F*

  10. Save the External Content Type

    https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F1.bp.blogspot.com%2F-Ww7JxllTyNg%2FVElBGEu9mvI%2FAAAAAAAAAlc%2FevzqNooYvfw%2Fs1600%2F8.png&container=blogger&gadget=a&rewriteMime=image%2F*

  11. Create External List

https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F4.bp.blogspot.com%2F-a2XlB8SCYl0%2FVElBC9k8VXI%2FAAAAAAAAAko%2F9ma9gwcUidw%2Fs1600%2F10.png&container=blogger&gadget=a&rewriteMime=image%2F*

Additional Information: 

In order to impersonate the user identity you need to create a secure store application and update the External Content type External System 

https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F3.bp.blogspot.com%2F-yzQ9y-nWdAw%2FVElBDOw9qzI%2FAAAAAAAAAks%2F7Fb9Pw2YcV4%2Fs1600%2F11.png&container=blogger&gadget=a&rewriteMime=image%2F*

following this blog steps you can connect to remote oracle DB without writing a single line of Code.

Happy SharePointing