Oracle SQL Developer 2.1
Oracle SQL Developer is a relatively new product included in the range of Oracle
products. It was first introduced to the world in September 2005, by its code name Project
Raptor. Raptor is a name many still cling to, despite being replaced very quickly with the
full product name, Oracle SQL Developer (and referred to in the rest of the book as SQL
Developer). The first production version was released in early 2006 and had many Oracle
customers both skeptical and excited.
SQL Developer is a Graphical User Interface (GUI) for browsing and working with
databases. Primarily developed for the Oracle database, it also provides a GUifor a
selection of popular non-Oracle databases. As technology has evolved, many developers
are looking for modern tools to assist them in daily tasks. A clean, easy to navigate
interface is essential and many tools are judged on looks, performance, and functionality.
Initially billed as a light-weight, clean, easy to install GUifor the Oracle database, SQL
Developer is gaining momentum in the market and broadening its scope in its offering.
The skeptics mentioned are ever concerned that Oracle is not really interested in the
product, backing up this concern with the fact that SQL Developer is free. Disappointing
as it is that “free” is equated with “poor quality”, the last couple of years have
considerably stilled many of these dissenting voices and the market using SQL Developer
continues to increase. Time will tell.
What This Book Covers
The book is designed to allow you to dip into any chapter that is your current area
of focus. Having said that, if you have never used SQL Developer before, are new to
Oracle and SQL, or have never used a GUitool before, we recommend that you read
the introduction, which gets you started slowly. You can then work through the first
few chapters in order. Once you have covered the first three chapters, you should be on
your way.
As the chapters progress, while they do not necessarily get more complex, they do rely to
some extent on existing knowledge. The latter portion of the book is not considered to be
more complex, but instead focuses on areas used by specific target audiences, such as
Oracle Application Express or migrating from non-Oracle databases.
We have loosely divided the book into the following three sections.
I: Getting started
The first few chapters introduce SQL Developer. Use them to get to know the layout of
the product and the essential features it provides. For those new to Oracle, or application
developers who spend the day using other development tools, these first few chapters
may be the only chapters needed.
In Chapter 1: Getting Started with Oracle SQL Developer, we provide general
information about SQL Developer, where you can find it, how to install it, and how to get
started. Using a simple example, you quickly walk through the product, touching a
variety of areas to get a feel for the navigation, the layout, and some of the features
available. We introduce the environment, how to navigate and manage the interface, the
database, and schemas used in this book. The chapter includes tips for customizing the
environment to suit your preferences.
In Chapter 2: Browsing and Editing Database Objects and Data, you will create, edit,
and update database objects, such as tables, views, sequences, and instance data (the data
in those tables), using data grids. You’ll also learn how to access and use the utilities,
wizards, and dialogs, which SQL Developer provides, to work with data and database
objects. Lastly, you will use SQL commands and scripts, accessing the code formatter
and other assistants that separate a GUitool from a command–line environment.
In Chapter 3: Working with the SQL Worksheet, you see that the SQL Worksheet
provides a scratch pad for all SQL, PL/SQL, and SQL*Plus statements and commands. In
this chapter, you learn to work with the SQL Worksheet and the commands it supports,
how to use code insight, templates, snippets, and formatting code. You learn how to
manage the environment and to work with multiple worksheets. We introduce the Query
Builder and how to write, test, and run SQL queries using the Query Builder.
In Chapter 4: The Power of SQL Reports, you look at the variety of SQL reports
provided by SQL Developer, why they are useful in daily tasks, and how to run and use
them. You’ll learn how to create your own reports, such as master-detail or drill-down
reports, and share your user-defined reports.
II: Delving a little deeper
These chapters are intended for all developers, perhaps more focused on those of you
who spend your days working with database objects and data. While not strictly advanced
features, there are areas here that can be more involved, and knowledge of how the tool
works will complement your existing knowledge of developing and working with code.
In Chapter 5: Working with PL/SQL, we start by reviewing various PL/SQL structures.
This chapter covers creating, editing, compiling, and debugging PL/SQL code. You’ll see
how the various search tools work, and use code templates and snippets. You’ll learn how
to refactor PL/SQL code, and search using the Oracle Database 11g PL/Scope feature.
Chapter 6: SQL and PL/SQL Tuning Tools. Although not designed to teach you how to
optimize your code, this chapter shows you the different utilities that SQL Developer
provides to help you see problem areas and work at fixing them. Utilities include using
Explain Plan, reviewing trace files, and auto trace.
In Chapter 7: Managing Files, you’ll see that SQL Developer provides an integrated file
navigator that allows you to browse the file system and open files from within the tool.
This chapter introduces the file navigator and the features it supports. Working in
conjunction with the file navigator, SQL Developer integrates with open source version
control systems. This chapter discusses the alternatives and shows you how to place your
code under version control and work within a team sharing code, by comparing and
merging code.
In Chapter 8: Importing, Exporting, and Working with Data, you’ll learn how SQL
Developer provides a number of utilities for moving or copying both the data structures
and the instance data between schemas and databases. This chapter introduces you to the
schema copy, diff, and export wizards and related export and import utilities.
III: Focus areas
The last few chapters are focused on specific audiences. We don’t specifically cover
complex features, but there are aspects of some of these features that require knowledge
beyond the scope of this book.
In Chapter 9: Database Connections and JDBC Drivers, we’ll look at the various types
of database connections available, how to set them up, and when to use them. We’ll look
at various authentication methods, such as LDAP, OS authentication or strong
authentication methods like Kerberos, and compare Basic, TNS, and JDBC connections.
This chapter also looks at setting up the required JDBC drivers and creating connections
to non-Oracle databases. The chapter closes with a section on managing your
connections.
Chapter 10: Introducing SQL Developer Data Modeler. Whether you just want a diagram
of how your tables connect, or you want to build an entity relationship diagram,
transform that to a relational model, and generate the DDL script to create the objects in
your database, the SQL Developer Data Modeler provides the tools you need. This
chapter introduces some of the modeling utilities available in SQL Developer Data
Modeler. You’ll also learn about the integration points with SQL Developer and the Data
Modeler Viewer in SQL Developer.
In Chapter 11: Extending SQL Developer, you’ll see that SQL Developer is an extensible
tool. This means you, as an end user of the tool, can add in your own features. These may
be small, providing just a single input or output field, and only require XML and SQL
knowledge, while others are more extensive and require Java skills. This chapter reviews
the various ways you can extend SQL Developer using SQL and XML.
In Chapter 12: Working with Application Express, you’ll see how SQL Developer
connects to and provides utilities for interacting with Application Express (Oracle
APEX). In this chapter, features you’ll learn to use include setting up the environment to
work with Oracle APEX, importing, browsing, and deploying applications. You’ll also
see how to tune and refactor SQL and PL/SQL code.
In Chapter 13: Working with SQL Developer Migrations, you’ll understand how SQL
Developer helps you connect to and browse non-Oracle or third-party databases. This
chapter looks at preparing the SQL Developer environment, and browsing the supported
third-party databases. The focus of the chapter is on migrating from a third-party database
to Oracle, from setting up the repository, through the capture and conversion phases, and
ending with data migration. You’ll also learn to translate pieces of code, such as
translating T-SQL to PL/SQL.
Standards and assumptions
Unless otherwise stated, the environment used in the examples and referred to throughout
the book is Oracle Database 11g Release 1, installed on Microsoft Windows XP. We use
the shipped Oracle sample schemas, in addition to using the SYSTEM and SYS users.
Oracle offers a free database, Oracle Express Edition 10g (XE), which you can download
and use for many of the examples. Some of the discussion is related to new Oracle
Database 11g functionality that is not available on any Oracle Database 10g release,
including XE. In the case where the feature is Oracle Database 11g specific, the text will
indicate this. However, the text does not list anomalies for each database version.
This book was written using and is based on Oracle SQL Developer 2.1. Screenshots and
menu items all refer to SQL Developer 2.1 and were accurate at the time of writing,
completed just prior to the production release of the software. Minor differences are
inevitable due to changes in the later stages of product development prior to production.
You can also use the book, and much of the content, as a guide for working with earlier
releases of SQL Developer. Where they are significant, we make note of the differences
in features that have changed from earlier releases.
Getting Started with SQL Developer
This book is divided into chapters that focus on the different areas or functionality
in SQL Developer. The progression through the chapters is from the more frequently
used features to those less frequently used. This initial chapter is all about preparing
your environment, installation, and getting started.
SQL Developer is easy to set up and use, so there is very little setup required to
follow the examples in this book. The best way to learn is by practice, and for that
you’ll need a computer with access to an Oracle database and SQL Developer.
This chapter, and indeed the rest of the book, assumes you have a computer with
Microsoft Windows, Linux, or Mac OS X installed, and that you have access to
an Oracle database. It focuses on the alternative installations available for SQL
Developer, where to find the product, and how to install it. Once your environment
is set up, you can follow a quick product walk-through to familiarize yourself with
the landscape. You’ll create a few connections, touch on the various areas available
(such as the SQL Worksheet and Reports navigator), and learn about the control
of the windows and general product layout.
Preparing your environment
Preparing your environment depends on a few factors, including the platform you
are working on and whether you have an early edition of SQL Developer previously
installed. First, you need to locate the software, download, and install it.
Finding and downloading the software
SQL Developer is available through a variety of sources as both a standalone
download and as part of the Oracle Database and Oracle JDeveloper installations.
SQL Developer is a free product, and you can download it from the Oracle
Technology Network, http://www.oracle.com/technology/products/database/
sql_developer. Use this link to reach the download for the latest standalone
production release. It also includes details of the release and is regularly updated
with news of preview releases and new articles. While SQL Developer is free to
download and use, you are required to read and agree to the license before you can
proceed with the download. The product also falls under Oracle Support contracts,
if you have a Support contract for the database, which means that you can log Oracle
Support tickets.
Downloading and installing the Java Development Kit
SQL Developer requires the Java SE Development Kit (JDK) ; this includes the Java
Runtime Environment (JRE) and other tools, which are used by SQL Developer
utilities such as the PL/SL Debugger.
For Microsoft Windows, you can download and install SQL Developer with the JDK
already installed. This means you’ll download and unzip the product and will be
ready to start, as there are no extra steps required. For the other operating systems,
you’ll need to download the JDK and direct SQL Developer to the path yourself.
Indeed, as many other products require a JDK to be installed, you may already
have one on your system. In this case, just direct the product to use an existing JDK
installation. For Microsoft Windows, ensure you download SQL Developer without
the JDK to make use of an existing JDK installation.
The SQL Developer download site offers a selection of download choices:
- Microsoft Windows (with or without the JDK)
- Linux (without the JDK)
- Mac OS X (without the JDK)
In each case, make your selection and download the required file.
The download sites for the JDK are as follows:
- For Microsoft Windows and Linux:
http://java.sun.com/javase/downloads/index.jsp - For Mac OS X:
http://developer.apple.com/java/download/
SQL Developer is shipped with the minimum JDK required. You can
download and use the latest updates to the JDK. You should be aware
that some updates to the JDK are not supported. This detail is posted on
the SQL Developer Downloads page for each release. Starting from SQL
Developer 2.1, JDK 1.6 is the minimum JDK supported.
Once you have installed the JDK, you can start SQL Developer.
Introduction to Spring Portlets