If
you're not
already a subscriber
to our newsletter you're missing out on a myriad
of tips and tricks you need to become a better, faster,
smarter developer. Subscribe now and ignite your
career.
Now
that we know what it is and on the assumption that you've decided to at
least give it a go, the next step is obviously to download it and
install it.
Oracle SQL Developer is available from OTN
(the Oracle Technology Network), subject to you agreeing with the
licensing conditions.
For the Windows
version there is a
choice of downloading it with or without the required JDK (Java
Development Kit). If you've already installed the required JDK or want
to install it separately, pick the file without the JDK. On other
platforms you have no choice, the JDK has to be installed separately.
On Windows the installation process is just a matter of unzipping the
file
to the required directory and then navigating to the directory and
running sqldeveloper.exe.
There is a
wide variety of tasks you can perform with SQL Developer and
usually more than one way of doing each of them. There isn't the space
to cover them all in this overview so we'll just have a look
at the basics to give you an idea of what you can do.
For a fast start watch our SQL Developer video tutorial.
Creating a
Database Connection
Once
you've installed Oracle SQL Developer, the first thing you need to do
is to define a
database connection using the connections tab. If
the connections tab is not showing you can display it by selecting
View|Connections from the menu (or use the shortcut keys Alt-v
c).
From
here you can create a new connection by clicking on the big green plus
sign or right click on the icon
of the disk with a plug on it (the empty
list of connections) and select "New Connection". This
brings up a form for specifying the details of the
database to which you want to connect as well as providing a label for
this connection.
If
you're likely to be connecting multiple databases it's a good idea to
include the database name in the connection name.
If you chose the
"Basic" connection type from the drop-down list you have to supply all
the connection details, whereas if you choose "TNS" you're given a drop
down list of available connections which makes things a bit easier
(assuming you're using TNS). The other choices are "LDAP" or "Advanced".
Once
you've created a connection, a little disk icon labeled with the
connection name appears as a new
node in your connections tree and the SQL window pops up. Defining the
connection and connecting to the database can be done separately and
any connections defined in one session are automatically remembered by
future sessions - unless, of course, you delete them!
Viewing/Modifying
Table Data
We learned earlier that Oracle SQL Developer provides a graphical interface to
the database and this provides you with one way of viewing and
modifying data.
When you expand your connection node (by clicking
on the plus sign next to it), you're presented with a list of all the
possible types of objects that could exist in the selected schema.
Expanding
any of these branches causes the list of all of the objects of that
type in your schema to be retrieved. This may take a while if you have
a schema with a large number of objects.
To
view and modify data in a table it's just a matter of selecting the
"Tables"
branch in the connection tree to load the list of tables owned by the
current schema, then selecting the table of interest. This
brings up
the table definition in the view window.
Clicking on the "Data"
tab in the view window displays the table data in a grid enabling
you to add, delete and modify the data in situ. To commit changes, you
simply click on the icon of the disk with the green tick or to rollback
you just click on the icon of the disk with the red curved arrow.
Using PL/SQL
Selecting a
pl/sql procedure or function from the "Procedures" or "Functions"
branch in the connections tree brings up the pl/sql view window which
enables you to view the code, modify,
compile and run it.
When
you run your code (by clicking the green arrow or using Ctrl+F11),
Oracle SQL
Developer automatically builds an anonymous block for you complete with
the appropriate
variable declarations and the call to your procedure or function with
the correct parameters. It even adds a dbms_output statement for you if
it's a function that's being tested.
The only thing you need to do is to modify
the code to initialise the parameters with the correct values and then
run it by clicking the OK button. Results are displayed in a
message pane which pops up automatically
when you click OK.
New procedures, functions and packages can be created by right clicking
on the
root of the procedures or functions branch in the connections tree.
How To Run Your
Own SQL Code
We've
seen how to view and modify raw table data using the data grid but if
your needs are more
sophisticated than that you can enter and run your own sql code in the
Sql
Edit window. To access this just select Tools|SQL Worksheet from the
menu or use the shortcut Alt-F10.
As
you type in your code a list of possible items is automatically (and
very quickly) displayed. By using the up and down arrow keys you can
navigate to the item of interest and select it by pressing
<return> or <enter>.
There is also a snippets side
bar which provides access to small pieces of SQL code such as
conversion functions and date formats to save you having to enter them
manually and for when you're unsure of the syntax.
This
was just a quick look at Oracle SQL Developer - it has far too many
features to be covered in a short overview like this. More details can
be found on
the
Oracle Technology
Network site.
Looking
for more Oracle training?
Learn quickly and easily from highly
experienced consultants with skills honed
over many years in the real world designing, building and testing
myriad, complex systems. See our partners for
Oracle
training in
New Zealand and
Oracle
training in the UK. All courses are led by
experts, can be customised to your exact requirements and are
offered on or off site.
