Unfortunately SharePoint Designer doesn't support connecting to Oracle database. So you may follow one of the following approaches:
1.Manually create a BDC Model for Connecting to Oracle Databases from the beginning How to: Connect to an Oracle Database Using Business Connectivity Services
2.Create a Web service to provide an interface to the external data exposed in the database
3.Use Visual Studio's BDC Model project and write the code for Oracle connection. Please look at this blog post Business Data Connectivity Model – Finder Method
I would recommend you to try out the product provided by the company where I work for, and name of that product is BCS Meta Man . It will allow you to generate both Database model and C# code automatically as soon as you drag and drop database tables to the design surface.
It supports Oracle and ODBC connection.
Please look at these blog post and video:
Business Connectivity Services and Oracle with BCS Meta Man
Oracle and the Business Connectivity Services
SharePoint 2010 Secure Store Service and Oracle
Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010
Business Connectivity Service Associations using Oracle Stored Procedures in SharePoint 2010
BCS filters and Oracle Stored Procedures with Parameters in SharePoint 2010
Please take a look at this sample BDC Model for Oracle database:
<?xml version="1.0" encoding="utf-8"?>
<Model Name="BCSMetaMan1Model" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
<LobSystems>
<LobSystem Name="BCSMetaMan1" Type="Database">
<LobSystemInstances>
<LobSystemInstance Name="BCSMetaMan1Instance">
<Properties>
<Property Name="ShowInSearchUI" Type="System.String"></Property>
<Property Name="DatabaseAccessProvider" Type="System.String">Oracle</Property>
<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="RdbConnection Data Source" Type="System.String">auriga</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String"></Property>
<Property Name="RdbConnection User ID" Type="System.String">hr</Property>
<Property Name="RdbConnection Password" Type="System.String">hr</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity Name="REGIONS" Namespace="BCSMetaMan1" Version="1.0.0.0">
<Properties>
<Property Name="OriginalTableName" Type="System.String">"HR"."REGIONS"</Property>
<Property Name="IsCustomCode" Type="System.Boolean">false</Property>
<Property Name="Title" Type="System.String">REGION_NAME</Property>
</Properties>
<Identifiers>
<Identifier Name="REGION_ID" TypeName="System.Decimal" />
</Identifiers>
<Methods>
<Method Name="GetAllREGIONSEntitys">
<Properties>
<Property Name="BackEndObject" Type="System.String">REGIONS</Property>
<Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
<Property Name="RdbCommandText" Type="System.String">select "REGION_ID", "REGION_NAME" from "HR"."REGIONS"</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
<Property Name="Schema" Type="System.String">HR</Property>
</Properties>
<Parameters>
<Parameter Name="returnparameter" Direction="Return">
<TypeDescriptor Name="REGIONSList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor Name="REGIONS" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<TypeDescriptors>
<TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" ReadOnly="true" />
<TypeDescriptor Name="REGION_NAME" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="GetAllREGIONSEntitys" Default="true" Type="Finder" ReturnParameterName="returnparameter" />
</MethodInstances>
</Method>
<Method Name="GetSingleREGIONSEntityByID">
<Properties>
<Property Name="BackEndObject" Type="System.String">REGIONS</Property>
<Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
<Property Name="RdbCommandText" Type="System.String">select "REGION_ID", "REGION_NAME" from "HR"."REGIONS" where "REGION_ID"=:REGION_ID</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
<Property Name="Schema" Type="System.String">HR</Property>
</Properties>
<Parameters>
<Parameter Name=":REGION_ID" Direction="In">
<TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" />
</Parameter>
<Parameter Name="returnparameter" Direction="Return">
<TypeDescriptor Name="REGIONSList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor Name="REGIONS" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<TypeDescriptors>
<TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" ReadOnly="true" />
<TypeDescriptor Name="REGION_NAME" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="GetSingleREGIONSEntityByID" Default="true" Type="SpecificFinder" ReturnParameterName="returnparameter" ReturnTypeDescriptorPath="REGIONSList[0]" ReturnTypeDescriptorLevel="1" />
</MethodInstances>
</Method>
<Method Name="GetAllREGIONSEntityIDs">
<Properties>
<Property Name="BackEndObject" Type="System.String">REGIONS</Property>
<Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
<Property Name="RdbCommandText" Type="System.String">select "REGION_ID" from "HR"."REGIONS"</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
<Property Name="Schema" Type="System.String">HR</Property>
</Properties>
<Parameters>
<Parameter Name="returnparameter" Direction="Return">
<TypeDescriptor Name="REGIONSIDs" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor Name="REGIONSSimpleElement" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<TypeDescriptors>
<TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="GetAllREGIONSEntityIDs" Default="true" Type="IdEnumerator" ReturnParameterName="returnparameter" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>
</LobSystems>
</Model>
Link to source