Analytics


Google

Friday, March 14, 2008

Oracle SQL Queries - Oracle SQL Developer Vs Toad

Oracle SQL Developer is a good substitute (which provides a visual interface for SQL Query into Oracle database) to Toad for basic sql programming.

SQL Developer
Pro:
  • Free
  • When exporting result to an excel file, it includes column header and also adds a sheet showing the sql statement used. (Toad does not include the header nor does it include the sql statement).
  • No installation Needed - just download and unzip the folder then the program can be executed immediately.
  • Supports TNS or non TNS (using jdbc). Does not need Oracle Client to work.
  • Easy to create stored package, procedures and functions. Can use compile to verify the code. (same as Toad)
  • Can do show plan (as long as the user has right to create tables).
  • Short cut keys like F9 to execute, F4 to describe the table etc (same as Toad)
  • Supports MS Access???
  • You can import data into the tables straight from an Excel spreadsheet.

Con:
  • Cannot perform automatic sql tuning.
  • Does not work with Oracle version before 9i (may access but will show error).
  • Doesn't work very well to run full pl/sql scripts - (non stored procedure/function).

Toad
Pro:
  • Can provide automatic SQL Tuning (but only Xpert edition).
  • Easy to create stored package, procedures and functions. Can use compile to verify the code.
  • Can do show plan (as long as the user has right to create tables).
  • Expandable - can add modules - DBA, Xpert etc.
  • Works with all version of Oracle.
Con:
  • The save as option to save to Excel does not include the sql statement).
  • Supports only TNS (SQLNet needs to be set) i.e. requires Oracle client installed and SQLNet to be configured prior to use.
  • Can only import data from text file and need to go through Oracle Loader (meaning you need to configure a control file).
Note: There is also a Freeware version of Toad. However, it expires every 60 days and need to be reinstalled.

Other free SQL front end I have tried (but I did not find them as robust when I tested them a few years ago):
There are a lot of other front end interfaces which I haven't tried but can be found in SourceForge.

JDBC Navigator provides a quick and easy way to insert records into a JDBC enabled database from a csv file.

No comments: