SQL Developer for Oracle
Oracle has released SQL Developer, former known, as project Raptor. SQL Developer have been made, for Oracle Developers, to be able easy, to write and debug their SQL and PL/SQL code. The introduction of this tool, underlines Oracle's commitment and strong focus on database development. SQL Developer have been based upon the JDeveloper framework - Its up running in no time. Both Raptor and SQL developer has its origin in the real old Enterprise manager - however Raptor had a serious improved UI, and now SQL Developer, with an even stronger UI.
SQL Developer offers many of the same facilities, tools like TOAD offers, when it comes to SQL and PLSQL development - it comes with scripting window, DBMS_OUTPUT, buffer size control, explain plan, procedure debugging, breakpoints, trace into and much more. However you cannot compare the two, TOAD and SQL Developer. SQL Developer is build for developers only - whereas TOAD offers in their professional edition, DBA facilities to deal with data files, tablespace, users, roles etc. You can however - do some of the DBA related things, if you remember the syntax.
Here is an introduction to SQL Developer, on how to get it up and running - and trying out a few things.
First things first. You need a running Oracle database, if it should make any sense installing, and testing SQL Developer. If you have not already done it, get a version of Oracle XE. Information on how to install it - you can find in my post, Oracle XE - Windows installation.
When your fresh Oracle XE installation is running, or you already have an Oracle database running on your system, you should download and install SQL Developer.
1. Download SQL Developer from here.
The file comes down as a self extracting ZIP archive. Unpack it when finished downloading. I would recommend you to put it somewhere like c:\programs\SQL Developer. Theres no real installation - finished unpacking, you are ready to go.
From where, you unpacked your download, run the file sqldeveloper.exe. You will be asked, about which files SQL developer should associate itself with upon startup.
First impression;
If you right click, on the connections entry, choose new database connection, you are prompted for details about a new connection. Start by giving your connection a new name; I call mine XE, specify your TNS description - if you cant remember - you will have to take a look at your tnsnames.ora file, located in your $ORACLE_HOME/network/admin catalog. In the same description window, you will find a test button, which is a TNSping - to see if we can connect. It reminds me of the old UI to Pro*C, back in the days. Anyway - I also specify my user credentials, user name SYSTEM, and password MANAGER and press connect.
If you have multiple oracle homes, it might be a good idea to set TNS_ADMIN, in your environment.
When you have done so, or you only have one oracle home, press connect.
To your left you see the navigation pane, it contain all object types for SYSTEM. On your top right window, you will find the data input window, and below that the result window.
You properly, have also noticed, that when you connected - your SQL developer, now have two navigation tabs on your top left. Connections is what we look at right now. The Reports tab - gives you access to a number of Data Dictionary reports, but lets stay with connections for now.
The design, looks and feel of the UI, is in my opinion, very good. Simple, yet with loads of functionality. Lets try a couple of things.
I know its not a DBA tool,- but lets just start out with creating our own tablespace, lets call it DEVELOPMENT. And lets also try to create a user to use that tablespace, lets call him KBIRCH. The idea is to create the user, assign him the proper database rights, and create some tables and procedures.
Currently we are connected as SYSTEM, so we have all the database rights we want. There is no tablespace tab in the navigation pane - only objects - so we have to use the data input window to create out tablespace;
CREATE TABLESPACE DEVELOPMENT datafile 'c:\u01\oracleexe\oracldata\xe\development.dbf' SIZE 20M;
That works fine, when finished with the CREATE TABLESPACE statement, I press EXECUTE. It places the specified datafile in the catalog I want. After that, I want to create a user, with proper rights to write to the DEVELOPMENT tablespace. For that you find the users tab in the navigation pane - click and point at CREATE USER.
I am creating my user KBIRCH, with the secret password KBIRCH. My default tablespace should be DEVELOPMENT, and my temporary tablespace should be TEMP. If you choose the SQL navigation tab - you can actually view the syntax.
OK that should be it - lets create a new connection, call it XE - KBIRCH, and connect. Don't logout - on your top right when your are connected as KBIRCH - there will be a drop down box - so you can shift between connections - yes you are allowed to have more that one open connection - which is a plus.
Ups - I cant connect. Seems I don't have the CREATE SESSION privilege. I also noted when creating my user KBIRCH - that there where no QUOTA to specify on my tablespaces - so if default is 0 - privileges will not help me - but lets see, how it works. I know that Oracle, made this as a Developer tool, that properly why, I cant set my QUOTA and properly also why, I have to write the syntax instead, of showing tablespaces on the navigation pane. However I personally find it as a minus to SQL Developer.
If you want to skip, creating several individual privileges, as I do below, skip that and do this;
GRANT CONNECT, RESOURCE to KBIRCH.
However just to show you i do as follows; I login, as SYSTEM, go to my data input window and execute the following statement.
GRANT CREATE SESSION to KBIRCH;
I log off as SYSTEM, and in again as KBIRCH. I go to my data input window and try to create a table, just to test if I, can write to my tablespace DEVELOPMENT. I cannot, I lack the CREATE TABLE privilege. OK I logout, and login as SYSTEM and execute.
GRANT CREATE TABLE to kbirch;
Switching between users, as mentioned, are done using the drop down box on your top right.
An annoying thing however - is the data input, in the data input window, remains the same, that is the things you entered as SYSTEM, also shows as KBIRCH. TOAD to compare SQL Developer with another tool - have separate windows for separate connections - which is very nice, and something I think is a minus for SQL Developer.
Having CREATE TABLE privilege, I try again and get
CREATE TABLE test (id NUMBER);
ORA-01950 no privileges on tablespace DEVELOPMENT.
So as stated before - i lack QUOTA on my tablespace. I return to my SYSTEM account and issue a;
ALTER USER kbirch QUOTA unlimited ON DEVELOPMENT;
Don't do and QUOTA UNLIMITED on TEMP, on a R2 of 10G as it produces an ORA-30041.
Instead of granting separate privileges, CREATE SESSION, CREATE TABLE etc - I could have executed the following statement, as mentioned above;
GRANT CONNECT, RESOURCE to KBIRCH;
Having created my table - go to the navigation pane and right click on the table. That gives you several options, export, statistics and more.
Right clicking, on an object in SQL Developer, is, as you properly have noticed standard functionality.
Lets try to create a PROCEDURE. Log in as KBIRCH/KBIRCH. Go to your navigation pane - scroll down to PROCEDURES, and pick CREATE PROCEDURE. I enter the following statement;
CREATE OR REPLACE PROCEDURE KBIRCH.TESTPRC2 AS
BEGIN
for i in (select sysdate from dual) LOOP
dbms_output.put_line(i.sysdate);
end loop;
END;
The procedure is compiles without any warnings, going back to your navigation pane - you should now be able to See the new procedure TESTPRC2 under PROCEDURES. Right click and choose RUN. Have a look in your output window, and you will See todays date.
If you - are creating procedures with in, out and in out parameters - you are also able to debug those parameters - you can grant execute to others, revoke and all what you basically need in a development environment.
Conclusion
SQL Developer is a good developer tool - however if you do a mix of development and DBA, its not what you need. I would for one, like to have the functionalities of tablespaces, roles, quotas on users available in the navigation pane. Also I think - that each connection window should be separated and individual with regards to SQL statements.
However all in all - i would recommend you to try SQL Developer - and are you new to database development - it will cover your needs perfectly.
Further informations
SQL Developer forums
Guider, Tutorials to SQL, PLSQL and more can be found here.