OK, after considerable head-butting & hair-pulling, I have got it through.

The problem is that the SqlDataProvider in DNN (for worse) has special treatment. The culprit is the following code in abstract DataProvider:

You can clearly see the hard-coded Provider NameSpace (although the assembly name is not hard-coded). So, DNN expects its concrete DataProvider to be always DotNetNuke.Data.SqlDataProvider. (For some strange reasons beyond comprehension, the Provider namespace is not discovered the same way i.e. Reflection, as it is done for other providers).

This affectively means that you can put your DataProvider in any assembly as you wish (remember, it should still inherit the abstract provider), but the Fully Qualified Name of the DataProvider should always be DotNetNuke.Data.SqlDataProvider.

What's more, when you put this assembly in the bin folder, there arises another problem. Now there are two classes with exactly same Fully Qualified Name, that is unacceptable to DotNet. That effectively means that you need to delete the DotNetNuke.SqlDataProvider.dll assembly from your bin folder. Although you can change the abstract Data Provider to find the concrete Provider namespace y Relfection easily (just copy the code from any other provider to do so), but this code is at the heart of DNN, and modifying it would render your installation utterly difficult to upgrade. So, deleting the DNN Core provider is the prefered way according to me.

Lastly, I wanted to customize only 10-12 method. So, I inherited the DNN SqlDataProvider. But with this came the same situation again. Although I had deleted the DNN's SqlProvider assembly from the bin, but my custom provider referenced it as it inherited from one of its classes. In effect, referencing my assembly in DNN copied the DNN's provider again into the bin folder, again creating namespace conflicts.

I seriously wanted to avoid rewriting all SqlProvider methods. SO, now what I have done is to copy the DNN SqlDataProvider's source class to my custom Provider assembly, renamed it to DNNSqlDataProvider, and then inherited it. I know this is a not a clean solution, but it provided me 2 advantages: I did not had to recode the entire Provider, and 2) While upgrading for a future version of DNN, I can just copy-paste the new DNN SqlDataProvider again, it there is any change to it, inherit it & see which methods need to be overridden.

Finally, the web.config part, it should look like following:

http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/111/threadid/171688/scope/posts/Default.aspx

Rename DNN's Provider or delete it, but keep the default provider's name to be SqlDataProvider. What's more, in the type attribute, the name of the Provider is inconsequential (I haven't tried with assembly name, but believe that invalid assembly name should should lead to a run-time exception). But you can type any garbage value as the Fully Qualified Name of the Provider class in the type attribute, and DNN would ignore it, as the this has already been hard-coded into DNN as shown above!!!

Hope, this would help many others!!!

In that case, I guess the Provider name has to be SqlDataProvider, OracleDataProvider or anything like that, which exactly matches "DBMS name" + "DataProvider".

As you can see from the abstract DataProvider code shown above from DNN code, DNN expects the DataProvider to be from DotNetNuke.Data namespace. Now, if I want to provide my own Provider for SqlServer, it needs to be named SqlDataProvider. Next, it needs to be in DotNetNuke.Data namepsace. As soon as I do that & deploy my assembly to bin, it creates namespace collisions. So, there is no choice but to delete DotNetNuke.SqlDataProvider.dll

In your case, I guess it is working becuase your Provider's Fully Qualified name is, DotNetNuke.Data.OracleDataProvider. As your class name is different coz you are using Oracle, it does not cause collisions. But anytime, you provide Provider for SqlServer, it would cause collision because of specific name requiremetns. That automatically means, I cannot have my own SqlDataProvider that inheruts from DNN SqlDataProvider, coz both would be classes with same name in same namespace.

Now, I see one more reason to use the name SqlDataProvider. Modules use reflection to create their provider instance. And their provider name has to match the main provider name being used, i.e. SqlDataProvider if Sql Server is the backend. That effectively means, that the Provider name has to be the DBMS name followed by the "DataProvider" suffix.