Database Problem Overview
I'm currently using Toad for my day-to-day work on our databases (queries, updates, small scripts, browsing of db objects, etc.).
My question is: since my version of Toad is old and buggy, which are the (possibly free, but not necessarily) alternatives to Toad?
The database versions we are using are 10g and 9i.
Thanks a lot.
Solution 1 - Database
Oracle's SQL Developer.
Solution 2 - Database
PL/SQL Developer from http://www.allroundautomations.nl/. Presented between a choice between Toad and PL/SQL developer, about half the developers prefer PL/SQL developer. I have to admit the code completion in PL/SQL developer is much better, but I still prefer Toad for the DBMS management functionality.
Solution 3 - Database
TOra is an another good option. I used the pre GPL version for years. It's a good simple SQL tool for Oracle.
It supports schema browsing, analyse plan, limited debugging plus all the stuff you'd expect.
Solution 4 - Database
I've used Squirrel-SQL (which is a Java/JDBC based program and will work with any OS or database that provides a driver) and the database development tools for Eclipse (Eclipse-DTP). Both are certainly sufficient for light database work. Both are free and open-source.
Solution 5 - Database
After trying other products for some months, I'm back to Toad.
And I have to admit that (IMHO, and for the type of use that I'm doing of it): I've found no alternative to Toad.
The indispensable features that are missing in other products are:
Speed. Toad is blazingly fast; SQL Developer starts to crawl if you open more than 2 or 3 connections. When you have to operate with time constraints, you really can't wait SQL Developer to open.
The "open a new window until commit" functionality (a new window is opened after an insert or update statement, and you are forced to commit or rollback). Really handy to avoid pending commits.
The "execute current statement" functionality (shift-F9). SQL Developer doesn't have it, or you are forced to select the query you want to execute.
Solution 6 - Database
I'm surprised that nobody has mentioned DbVisualizer yet. Our company has been using it for a few years and everybody seems happy with it. It's powerful, runs on multiple platforms (Java-based) and supports different database systems (Oracle, DB2, MS Access etc). It has a free version; a commercial version is available if you need more features.
Solution 7 - Database
Use Textpad or another text editor.
For me PL/SQL development is a process that has evolved over time. I've tried to apply continuous improvement to SQL development and it has worked out wonderfully for me. (for more on continuous improvement/Kaizen see link text)
I found PL/SQL IDE tools to be unstable.
I've had several crashes of Quest Software's TOAD as well as Quest Software's SQL Navigator (I've been using it since Version 3).
I lost work.
I tried other IDE PL/SQL tools.
These tools also crashed.
I lost work again.
I got frustrated.
I do not trust PL/SQL software development to any of the PL/SQL IDE tools out there.
Here are my PL/SQL coding core practices:
- Export code using Quest Software TOAD
- Use a Cygwin bash script to move files into the appropriate directories
- Compare versions via BeyondCompare (if needed)
- Check code out of WinCVS / CVSNT (if needed)
- Edit using TextPad
- Compare versions via BeyondCompare (if needed)
- Check code in to WinCVS / CVSNT (if needed)
- Use a Cygwin bash script to create a master changes file.
- Import code back using Quest Software TOAD
An even more lengthy explanation:
I use Quest Software TOAD to export all PL/SQL and table DDL code to the filesystem.
In the Database menu -> Export -> Source Code
In the Database menu -> Export -> Table Scripts
This gets me individual files for each database object.
I move these files (Cygwin bash script) in directories
based on the file extensions.
*.prc files in /procedures
*.fnc files in /functions
*.pks and *.pkb files in /proceudres
*.trg files in /triggers
*.vw files in /views
*.sql files in /table_scripts
These files are initially checked into CVS.
(I use WinCVS/CVSNT server side)
I Beyond Compare each file version exported by TOAD
with the version already in CVS.
I ensure that the CVS sql repository is up to date.
In other words I need to have a good starting baseline.
I then use TextPad to edit the PL/SQL code.
I pre-configure my Textpad with SQL syntax files
to make it easier on the eyes
After editing, I Beyond Compare each edited
file version exported with the version
Luckily, WinCVS allows you to use an external
diff (Beyond Compare) which comes in very handy.
I load the new/changed code via TOAD to a test schema.
In the SQL Editor menu -> Load and Execute a Script File
I test the code out. (do some debug runs)
If the code tests out, I check the code into CVS.
At the end, I use Cygwin bash (and a bash script I've written) to create a master changes file. This master changes file contains all of the changes that need to be applied to bring the live schema up to date. This saves me a lot of time.
I then load the new/changed code via TOAD to a live schema. In the SQL Editor menu -> Load and Execute a Script File. That's about it. Software engineering is about process, versioning (CVS) and automating builds (bash script).
The biggest lesson out of all this (that have made me 10 times more productive) was switching from DB-based PL/SQL IDEs to simple ASCII text files. KIS in action.
If a copy your code resides in an ASCII file you avoid:
mucking up the DB
locking up DB objects
iffy DB based revision control tools (if any)
iffy DB diff tools (if any)
losing code due to IDE crashes
losing code due to DB crashes / shutdowns
losing code due to concurrent editing (this can happen if two or more PL/SQL developers edit the same procedure)
Instead if you handle all PL/SQL code in filesystem ASCII files you have
your choice of text editors (TextPad,notepad++,vi,etc)
your choice of revision control systems (CVS,svn)
your choice of text filtering/handling/scripting systems (I like Cygwin bash)
your choice of diff tools (Beyond Compare,WinDiff,diff)
your choice of DB tools (I can use TOAD, SQL Navigator) for importing and exporting the PL/SQL code to files.
I wanted a version history of all code changes.
I wanted to get everyone working together and prevent developers from stepping on each other's toes.
I wanted the freedom to choose my tools.
The side effect of this is that I handle all of the DB code in the filesystem during rapid development.
Just my 2 cents.
Solution 8 - Database
I'll toss in my vote for SQLPal. It's free. Most of the features it has works really well (auto-competition has never seemed to work). I'm using it against a 10g database daily and it works great.
Solution 9 - Database
I've been using Golden from Benthic Software as a SQL*Plus replacement for a long time and love it.
Solution 10 - Database
If we had to pay then I would strongly recommend PL/SQL Developer for database code writting and TOAD for administration tasks.
From the free solutions I've been using so far especially four of them: (Oracle) SQL Developer, SQL Tools, SQLPal and TORA.
- I've found SQL Developer as the one with the most utilities. Unfortunately its java GUI makes it prone to hanging too much often.
- SQL Tools is a very nice instrument except one thing: it has not an auto-fill functionality / code-completion (e.g. table names and columns). If you could pass this issue then ... go for it.
- SQLPal it's good as long as you have to look at only one database
object at time. I just don't understand why displaying a simply
new window is, in fapt, meaning to open a completely new instance
of the application. It has its auto-fill functionality but that makes it very laggy. I would place a minus for its JDBC only available connection option. Such limitation makes it sometimes to hang while waiting to retrieve database metadictionay information.
- TORA - it's a nice tool except the code completion ... I just couldn't make it working. And from the forums I could get that this is a pretty common problem. Otherwise its GUI is almost flawless.
My conclusion: I hate to say it, I am an open-source fan but ... simply buy PL/SQL Developer / TOAD if you have to work intensively with such tools .
Solution 11 - Database
I would also recommend Oracle's SQL Developer, however I've found [WinSQL] to be quite good as well. SQL Developer is free and WinSQL has a community version that is quite robust, and works well against other platforms too (I also use it for working with Netezza).
: http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp "WinSQL"
Solution 12 - Database
I've used OraDeveloper by DevArt to connect to Oracle databases. It's nice because it plugs into Visual Studio in much the same way as the SQL Server databases can be queried through Server Explorer. It is not free, but in our case it came free with the purchase of OraDirect .Net, which is a new provider that allows Oracle to work with Microsoft's Entity Framework.
Solution 13 - Database
Having done 3 years in one job using TOAD, and now 5 years in current job using PL/SQL Developer, I would say overall TOAD is a better tool, but if you are specifically developing stored procedures in PL/SQL, PL/SQL Developer is better.
Don't forget get to set up the context sensitive help (under Help - HTML Manuals)
Solution 14 - Database
You could also use sqltools, which some of my colleagues prefer. I never got used to it, but looks powerful.
Solution 15 - Database
I use PL/SQL Developer for running multiple queries and stored procedure development, as it's rock solid, and you can kill the process via Task Manager, and get back all your windows upon starting up again. I use TOAD for browsing, as that functionality can't be beat. But it's flaky on me, hanging, etc, although that is probably my computer.. I would buy both. I have both open now.
For accessing odd sources (OSISoft PI, JDE on AS400, 4D, Access, Excel), I use Advanced Query Tool. It's great to be able to use SQL against an Excel sheet of a proposed table, to create your DDL.
Solution 16 - Database
I love Toad. I had to switch to Embarcadero's Rapid SQL for two months and it damn near killed me. So while I don't know of any products that are better than Toad, I can say without reservation that Rapid SQL is wholly inadequate.
Solution 17 - Database
We are using http://www.razorsql.com/">RazorSQL</a> for Oracle and also DB2. It is low cost and has a ton of features.
Solution 18 - Database
PLSQL Developer very good for Oracle DBMS, specifically for Stored Proc, but not so well for the DBMS management.
Solution 19 - Database
Just to throw it out there - did you know you can grab the beta of Toad 4.1 and use that for free? The betas are always free to the public.
Solution 20 - Database
I've used Embarcadero DBArtisan (not free)
I thought it was good.
Solution 21 - Database
I second the SQL Developer comments. FWIW, the newer versions of Toad are much more stable than the golden oldies were. I'm using Toad 9.7 and it's quite good.
Solution 22 - Database
I'm using PL/SQL Developer, which I think one of the best oracle database tools, it's lite and fast, I have one copy in my USB flash to allow me to use it in any pc(you just copy it from the program files folder and place it in the flash and it will work fine).
Also I bought OraDeveloper from DevArt three months ago, it's has some good features, but it's slower than PL/SQL Developer and require .Net framekwork. but the good thing that it has direct connection to Oracle, so you can use it from any PC without requiring Oracle client to be installed.
Another tools is EMS SQL Management Studio for Oracle, which looks nice, and have some nice features specially with import/export from other format. http://sqlmanager.net/en/products/studio/oracle
and for free one you can use AnySQL Maestro, which has support for Oracle/MySql/Sql server and offer AnySql Maestro for free http://www.sqlmaestro.com/
Solution 23 - Database
I've tried most of the tools suggested above, and found that I prefer this one over all of them (including TOAD): SQLDetective
Solution 24 - Database
SQL Studio for Oracle is really nice...
Solution 25 - Database
Dreamcoder offers good set of tools for Database Management.
Solution 26 - Database
I prefer both Toad or SQL Developer. But I give more preference to Toad because of features like-
> If you want to update your select result in toad, just add the ROWID > is your select statement. Once you select the records with ROWID then > you can edit/update your result grid.
Also, the session and Database monitoring monitoring features are much better in Toad. The only downside of Toad is that it requires Oracle SQL Client to be installed (which may be the case with any other non-Oracle branded tool), on the other hand Oracle SQL Client installation is not necessary for SQL Developer.
Solution 27 - Database
Worked with PL/SQL developer. It rocks.