I found a useful and important add-in for those who deal with Oracle databases using Visual Studio. Oracle Developer Tools for Visual Studio.Net. ODT for VS adds an Oracle Explorer, similar to the Data Explorer built in to VS. It has an incredible amount of functionality built in.
I already mentioned the Oracle Explorer, which gives you a tree that lets you examine your tables (and columns), views, stored procedures, packages, sequences, and all of the other objects Oracle supports.
There’s a plethora of designers and wizards that will allow you to create and alter the aforementioned objects. They work by generating SQL that you can preview before it’s applied.
The feature I find most useful is the PL/SQL editor. Right inside VS I can now write my stored procedures. But what’s really powerful is I can set a breakpoint, and step from my VB.Net or C# code right into the stored procedure, step through the stored procedure, then back into my application. THAT is useful.
You can obtain ODT for VS directly from Oracle, at no cost. http://www.oracle.com/technology/tech/dotnet/tools/index.html or http://shrinkster.com/lry.
I did run into one issue after the install. Oracle installs it’s ODP driver and creates a new Oracle home for your machine. In order to make the connections I had to copy my tnsnames.ora, ldap.ora, and sqlnet.ora files from my old oracle home to the one for 10.2, by default it’s in C:\oracle\product\10.2.0\client_1\network\ADMIN. I found this solution and some other interesting tidbits at the FAQ: http://www.oracle.com/technology/tech/dotnet/col/odt_faq.html or http://shrinkster.com/ls0.
I’m not going to regurgitate a lot of how to here, instead I’ll refer you to a good article on the Oracle site, at http://www.oracle.com/technology/oramag/oracle/06-sep/o56odp.html or http://shrinkster.com/lrz. This article has the basics to get you up and running.
If you work with Oracle databases, this is a must have add-on for your Visual Studio environment.