You can connect to Oracle through the BDC in two ways.
1, Using the Oracle .NET components
2, Using ODBC
If you use ODBC connection string to retrieve data from Oracle XE (or “Oracle Database 10g Express Edition”) and if you refer to www.connectionstrings.com then the connection string should be looked like this –
Driver=(Oracle in XEClient);dbq=111.21.31.99:1521/XE;Uid=myUsername;Pwd=myPassword;
If you use the connection string above, you’ll be able to be connected to Oracle XE using BDC Meta Man, drag and drop the tables, create associations,etc. You can generate the ADF and it will import into your Shared Services Provider without any error, but as soon as you try to view the data in the BDC web parts you’ll see the following error:
“An error occurred while retrieving data from <InstanceName>. Administrators, see the server log for more information”
To fix the above mentioned error you need to make a small change in the connection string of Oracle XE. At the end of connection string you need to add “Trusted_Connection=yes” and data will be retrieved properly.
