I solved a problem identical to the description of this problem with SQL SERVER and you can see my post
here;
and I’ll share the solution for oracle below.
After you have configured the Oracle data source (and worked your way through the errors) and the data source appears in the library - the problem cannot be solved with the configuration tools in SPD.
Go to the ‘_catalogs/fpdatasources’ folder in the folder list panel of SPD. Find the XML file that corresponds to the data source in error as it appears in the data source library.
Double-click on the XML file to open it up in SPD. The find the connection string attibute.
Simplify the connection string to just: Data Source, User ID and password. (E.G. “Data Source=XXX;User ID=XXX;password=XXX”
~ Replace XXX with your parameters.)
Set the attribute ProviderName=”System.Data.OracleClient”
If you don’t have an Oracle provider for the SPD that works, I used _designer:Provider=”MSDAORA” and it worked form me.
Save the file and close it.
Test the data source in an existing data view or create a new data view.
I hope this solution the works for you.
Good Luck!