TOAD has been basically the only good SQL development option available for a long time. There were some free tools out there, but none of them came even close to the type of features that TOAD provided. Being an Oracle developer myself, I was bored with just working on TOAD since so many years.
One thing that I always used to wonder is that why Oracle does not provides a good option for SQL development. The only option available from Oracle was SQL Plus, and that was very basic. Working on SQL Plus, instead of TOAD was like working in DOS instead of Windows. You get the idea.
Down the line, some good free options came up, most noteworthy being TOra. But Oracle still did not jumped into this field. Finally, Oracle has made the plunge, and launched SQL Developer – the graphical IDE for SQL Development. Most interestingly, Oracle has kept it completely free for any type of use (commercial or personal). So, if you have been like me and stuck with TOAD for too long, we have a good option now, and this would end up saving some money as well.
SQL Developer started out with a basic feature set, but it had many quick releases, and it has reached at the point that I am very comfortable in using it for actual Oracle development. It is a graphical tool that supports all the Oracle versions from 9 and above. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks.
Here are the detailed features of this free SQL Development tool from Oracle:
- Create and store connections for multiple DB. It can even store password and connection details. Apart from Oracle, you can also store connections for TimesTen, MySQL, SQL Server, MS Access and Sybase.
- SQL Developer comes with a nice object browser. All the objects are shown in Tree form, and similar objects are grouped together (like packages, tables etc.)
- You can choose to edit any object by using context sensitive right click menu directly in the Tree browser. The menu shows you the option specific to the object that you have selected. For example, menu for Table gives you option to rename table. You can also edit multiple objects together.
- SQL Developer comes with a very good option to create new objects. You can create all the supported object types using the object specific dialogs. Some create dialogs also come with advanced options. For example, you can specify sequences and before-insert triggers for populating a column with values on table creation dialog.
- SQL Developer has nice options to query data. You can query data for a single table directly, or run a complete query. SQL Developer also has support to display CLOBs and BLOBs, which is a rare feature in free tools. While viewing data in the grid, you can sort and filter the data, as well as see it in a single record view.
- SQL Developer also comes with a visual query builder that can be used to create simple queries by those who are not familiar with SQL. The query builder can be mastered easily by those who have done some work in Microsoft Access.
- SQL Developer makes data export very easy. You can directly export data from the grid in XML, CSV, SQL Insert, SQL Loader, text, HTML, or XLS form. This is one of the features that I use most. So, whenever I need to send data to someone, I can simply run a query to get data in result grid and then directly export that in an Excel. Very cool!
- SQL Developer also lets you import data. You can import data from Excel file or CSV file. SQL Developer can also create a new table for you, and then import data in that table. This option comes really handy if you have an Excel that you quicly want to dump in some table to query it further.
- Apart from data, you can also export DDL using SQL Developer . You can export DDL for a single object or multiple objects.
- SQL Developer also comes with a cool feature of Schema Compare. Basically, if you have objects in 2 different schemas and want to see difference in them, SQL Developer can easily show that to you. SQL Developer can also create a script for you to remove those differences.
- Another great feature of SQL Developer is option to debug SQL and Procedures. You can choose to run any procedure, specify the input parameter and then step through each step of execution to see exactly what’s going on. This feature can save you a lot of time in debugging your procedures.
- Apart from all the great features above, SQL Developer supports version control, comes with some built-in reports, and option to migrate from third party databases.
SQL Developer is really good, and very full featured. I am glad that Oracle chose to make this completely free. This is the best free replacement for TOAD that I have ever come across. You can couple SQL Developer with the free version of Oracle database, to have a completely free setup of database, and development tool. If you are looking for some tool that has some database management capabilities as well, you can try the open source product TOra.