Today I will talk about how you can manage Oracle with Toad Extension plugin for Visual Studio 2010 database development tools. As mentioned in a previous post, this plugin had two flavors; freeware and commercial. The following table shows the differences between both versions.

 

Usage

The extension is easy to setup and its usage is much like SQL Server counterpart. Once installed, there is a new menu item in VS2010:

Like SQL Server, there is a wizard that helps you setting up a database project and optionally imports an existing schema (User) structure.

Solution explorer

Schema import

Once the project has been created, you can reverse engineer an existing schema. Even though you used the project wizard, you can import several schemas to the project. And, one thing you need to be aware of is that the current version of the extensions will not prevent you to import one schema twice. The team knows about this and it should be corrected pretty soon.

Project properties

The project properties are very simple in a sense that they are limited compared to what we have with SQL Server. This can be very easy to explain since we are at V1.0 of the product. One thing I would like to have is the option to drop or not existing objects in the database if they do not exist in the project.

Folders and files

One thing I like a lot is the comment folder in the solution explorer. This folder contains all comments that the data architect, the database administrator or the developer added to a table or its columns. Also, all sql files in solution explorer have the “.osql” extension that presumably stands for “Oracle SQL”.

Files are organized by object types. As I told my Quest contact, it would have been nice to have the option to organize files by schema, which is the default behavior with SQL Server.

Schema view

The schema view is about the same as its SQL Server counterpart. Rename refactoring is fully supported as well as dependency viewer which I found very powerful and well implemented within Oracle projects. In Oracle, a schema is not the same as SQL Server schema. It’s a user:

When we open the HR schema’s file, it displays the following script:

The reason I am bringing this is because it explains why “Move to schema” refactoring has not been implemented. A schema in Oracle is a user, not the same abstraction layer as found in SQL server.

 

To be continued…

In my next articles on the topic, I will talk about data and schema comparison as well as schema deployment. Stay tuned J!