Oracle SQL Developer - A New GUI For Database Development
Categories: Database Administration, Information Technology, Oracle
At long last Oracle has come up with a modern product to replace SQL*Plus.
Oracle SQL Developer (formerly Project Raptor) was released last month with little fanfare; however its release represents a quantum leap in functionality and ease of use over previous Oracle provided SQL development tools.
SQL Developer is Oracle’s new, free graphical tool that enhances productivity and simplifies database development tasks. With SQL Developer,you can:
- Browse database objects
- Run SQL statements and SQL scripts
- Edit and debug PL/SQL statements
- Run provided reports
- Create and save custom reports
Now all these features exist in Quest Software’s product Toad for Oracle so why switch? Well, here’s the hook… SQL Developer is free. Yup, free. It also has another strategic advantage over Toad… SQL Developer is cross platform! I am currently running it side-by-side on Mac OS X and Windows XP, and there is also a Linux version available. The Mac and Windows versions are both extraordinarily easy to install, configure, and use. I can only assume the Linux version follows this trend.
So what’s the down side? Well, I’m personally disappointed that they changed the name from “Raptor” to “SQL Developer”, but that aside, you can’t use it on databases earlier than 9.2.0.1.
A small problem for Toad users will be adjusting to the date format in SQL Developer. Toad defaults to showing the time when displaying date data types while SQL Developer will only show the date. The solution to that is to add a TO_CHAR around the date column in the query (for more information on the TO_CHAR function, check out the Displaying Dates section of my article Oracle, SQL, Dates and Timestamps). Toad users may also miss being able to click on the header of a column to change the sort order of the output, but this can also be overcome by adding an ORDER BY clause to the query.
Setup was quick, taking less than a minute to download, unzip and connect (yes, one minute, you’ve probably spent more time reading this than it takes to install). As you’re setting up SQL Developer you’ll notice that it does not require a TNS Names or other descriptor file to find databases. When you set up a connection you are prompted for the hostname and SID of the database. This assures maximum flexibility but does run the risk of confusing end users, since they can nickname the connections anything they want to.
Below are some screenshots of SQL Developer, but if you’re interested, just go out and get it. It’s free after all, what do you have to loose.
Click on the thumbnail for a larger image.
May 12th, 2006 at 12:37 pm
[…] Marc did a good demonstration of SQL Developer, but this product is hard to appreciate until you’ve used it. Thankfully it’s free, so there’s no good reason not to test drive it. […]
June 15th, 2006 at 9:36 am
OK, I like SQL Developer, but the whole not viewing the time in the datetime fields is really start to get on my nerves. Why should I put the to_char function around it, it is a datetime field?! I hope they fix this bug soon.. Has anyone heard anything about it? Still no updates..
June 15th, 2006 at 9:43 am
I agree Chris. While it’s probably defaulting to the time format parameter for the database, there should be a preference to override it and show hour/minute/second.
My other beef with SQL Developer is having the extra step to show all results in the database tree when you are looking at more that a few users, tables, or whatever. Again, this should be a preference.
But, they are still in version 1 and only a few months out of the gate on this one. Hopefully there will be some enhancements soon.
Jon
June 22nd, 2006 at 5:46 pm
This is a workaround, and not a great one, but it might help someone out there.
http://www.oracle.com/technology/products/database/sql_developer/howtos/howto_policy_date_formats.html
and this is from the Readme file:
4.3 Support for Locale-Sensitive Date and Number Formatting
SQL Developer uses current database NLS session settings to format date/time and numeric datatypes. If the settings are changed with the ALTER SESSION command, SQL Developer reads the new values from the database and use them for subsequent formatting.
Do not change the session time zone with ALTER SESSION. To switch the time zone used for formatting, change the client O/S time zone setting and restart SQL Developer.
hope this helps.
June 22nd, 2006 at 9:03 pm
Thanks JP! I’ll have to try that.
November 28th, 2006 at 4:27 pm
Actually there are some unfortunate drawbacks to SQL*Developer: It will not talk to an 8i rdbms. (Toad can). There is also no easy way to run a command or a script or changing users without using the mouse to click on a a button (Toad also has some of these limits). For those of us that became touch-typists over the years or dealing with oracle (going back to v6 in my case) this is a severe slow down of work. Sure, SQL*Developer has a Tools->SQL*Plus option, but for those of us who jumped to Macs so we could get top of the line intel laptops that run on a *nix-ish underlayment, and therefore cannot instal SQL*Plus (Power PC chipset macs only, and since we have SQL*Developer oracle’s basic response is don’t hold your breath) waiting.
December 11th, 2006 at 9:20 am
I just downloaded 1.1 sqldev from oracle and was able to set up the date time format. But for some reason, I cannot get the NLS preferences to display the date in the correct timezone (CST). ANy help out there?
January 18th, 2007 at 3:56 pm
Just worked out through help that:
ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH:MI:SS’;
run in an SQL session will set the date to show the time as well, for as long as the current session lasts. This may be obvious to some, but not to me!
January 18th, 2007 at 9:05 pm
Paul,
Thanks for sharing. I had figured that would work in SQL Developer like it does in SQL*Plus but I honestly hadn’t bothered to try it.
February 28th, 2007 at 4:17 pm
Unfortunate side effect of that is:
Out core product was mishandling datetime issue from the start (still is).
Since they format dates from strings, strings from dates, etc, they RELY on the NLS_DATE_FORMAT to be very specific.
If I change that, their app get date parsing/format errors.
Every effort to convince them that the CODE should format the date string and parse via same format, they resist due to the “complexity of date-time and timestamp issues”
Yes. Up until recently, their dates were stored as Varchar2. Logic doesn’t seem to work.
June 14th, 2007 at 10:48 am
RE: viewing timestamp: Instead of having different formats specified in NLS for Date and Date/time, put your preferred format for viewing dates in the “date default” NLS setting, then you won’t have to constantly do Alter Sessions.
June 20th, 2007 at 3:23 pm
On the NLS topic, we are working w/ French chars in the DB and when either of the following are entered - œ Œ and Ÿ - we see a ¿ char via Toad or Sql*Plus
But when viewed via Oracle SQl Developer the chars display properly.
No changes were made to local NLS parms or Oralce Session NLS parms - Any thoughts?
Suspecting the JDBC drivers at this point but have no real evidence..
December 26th, 2007 at 1:40 am
I dont no about Oracle SQL Developer. So Please Tell me .
How to create a database for Connect. And How to Create Password and User id .
And After how new connection. and
What write Hostname and SID Or Service Name
August 9th, 2008 at 9:07 pm
I used MySQL and PostgreSQL before and now using Oracle 11g in my office.
I recommend Navicat GUI.
Recently, I visited their site and found that Navicat released a new Oracle GUI - Navicat Oracle Lite. It’s free version.
Download URL : http://oracle.navicat.com
News : http://blogs.navicat.com
I think Navicat is quite intuitive and easy to use.