Starting with Oracle Developer Tools for Visual Studio

Having to develop a new Oracle package I decided this was a good opportunity to start exploring the ODT add-on I had installed in Visual Studio 2008 a while back. I followed those steps:

• Created an empty solution
• Added an Oracle Database Project in Other Project Types (not to be confused with a Visual C# / Database / Oracle Project)
• Watched the project being added to the solution with folders for all the different types of Oracle database objects
• Added a new Package to the Packages folder
• Replaced the code templates with my own code
• Run the script to create the package
• Refreshed the Server Explorer pane and watched my package being added to the packages node
• Added the solution to the Source Control

Word of caution

After that initial setup I ran into the same issue I had run into with SQL Server 2005 last year.

In order to run or debug your package I have to be on the Server Explorer side and any changes made to the PL/SQL code will be saved to the database. I then need to copy and paste back to the Solution Explorer in order to check in those changes. Or I can only make the changes in the Solution Explorer, Run to commit the changes to the database and then go and test in the Server Explorer.

Either way this requires great discipline and is prone to mistakes. Things got “worse” when I tried using another PL/SQL editor (like Oracle SQL Developer or PLSQL Developer) because of the greater context switching and the increased chances of forgetting to copy back to Visual Studio and committing to TFS. I don’t know of an ideal single-step solution yet.

I’ve started using a simpler approach based on the option in Server Explorer called “Generate Create Script to Project…”.  Using that approach both the specification and the body have to be generated separately. Also, each time a new file is generated it overrides the previous one in the Visual Studio project.

The code editor itself needs some polish. For instance it doesn’t have the code completion that Oracle SQL Developer has. The code collapsing partially works. Stored procedure and function names don’t always appear in the drop-down list above the code editor pane.