Oracle SQL Developer – A New GUI For Database Development

raptor_image.jpgAt 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.

SQL WindowPackage Editing


Table BrowsingReports

Click on the thumbnail for a larger image.

oracle, sql, sql developer, database development, database administration, dba

21 thoughts on “Oracle SQL Developer – A New GUI For Database Development”

  1. 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..

  2. 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

  3. 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.

  4. 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.

  5. 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?

  6. 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!

  7. 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.

  8. 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.

  9. 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..

  10. 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

  11. Just switched fromm Toad to Developer and can’t find the equivalent of Shift F9 in Toad, which means execute the command where the cursor is.

    Only workaround I’ve found is to select the entire statement and then hit F5. It’s annoying, at best.

    Does anyone know if there’s an equivalent to Shift F9?

  12. Kathy,
    You just need so set up an accelerator key for that. If you go to tools->preferences and then click on Accelerator, and then you can find the command that you want to run then you can set the Shift-F9 or whatever command you want for that command.
    Don

  13. Quick note — if you have specific needs for date/timestamp formatting, you can set them once under preferences/database/NLS parameters.

  14. Kathy,

    I have found that if you terminate every statement with a semi-colon in Developer, then F9 will just execute the statement where the cursor is.

    Mike.

  15. Thanks for the tip #8 Paul about –
    ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH:MI:SS’;

    SQL Developer did not display timestamp until I ran this command.

  16. Yes, tip #8 works
    but with proper single quotes (for those who copy and paste from this webpage) and with 24HR clock

    ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’;

  17. Does the oracle sql developer display a datetime field in the oracle server’s timezone or in my local os timezone?

Leave a Reply

Your email address will not be published. Required fields are marked *