Oracle SQL Developer for Database Developers

Oracle SQL Developer for Database

Developers

An Oracle White Paper

September 2008

Oracle SQL Developer for Database Developers Page 2

Oracle SQL Developer for Database Developers

Introduction........................................................................................................3

Audience .........................................................................................................3

Key Benefits ...................................................................................................3

Architecture....................................................................................................4

Key Features .......................................................................................................4

Managing Connections .................................................................................4

Additional Authentication and Connection Methods .........................5

Grouping Connections ............................................................................5

Exporting Connections............................................................................5

Working with the SQL Worksheet .............................................................5

Executing Statements in the SQL Worksheet ......................................5

Editing Code in the SQL Worksheet.....................................................6

Browsing the Database.................................................................................7

Creating and Editing Schema Objects .......................................................8

Producing SQL Scripts .................................................................................8

Database Export .......................................................................................8

Schema Copy and Compare....................................................................9

PL/SQL Development.................................................................................9

Creating and Editing PL/SQL in the Code Editor .............................9

Running PL/SQL Procedures, Functions, and Packages .................10

Hierarchical Profiler ...............................................................................11

Debugging PL/SQL...............................................................................11

Additional PL/SQL Debugging Features ...........................................12

Remotely Debugging PL/SQL.............................................................12

Database Reporting.....................................................................................13

Version Control ...........................................................................................13

Migration.......................................................................................................14

Overview..................................................................................................14

Supported Databases..............................................................................14

The Migration Process ...........................................................................14

Translating Code.....................................................................................14

Extensibility..................................................................................................15

A Few Basic Extensions ........................................................................15

Conclusion ........................................................................................................16

Oracle SQL Developer for Database Developers Page 3

Oracle SQL Developer for Database Developers

INTRODUCTION

Oracle SQL Developer is a free graphical tool that enhances productivity and

simplifies database development tasks. Designed for Oracle Database developers,

SQL Developer simplifies development cycles and reduces the need to buy non-

Oracle tools for developing and debugging SQL and PL/SQL code. Using SQL

Developer, users can browse, create and modify database objects, run SQL

statements, edit and debug PL/SQL and can run reports from an extensive list of

predefined reports or create their own. Users can also create Database

Connections for non-Oracle databases MySQL, Microsoft SQL Server, Microsoft

Access and Sybase Adaptive Server for object and data browsing and can migrate

the objects and data from these platforms to Oracle.

The addition of this tool underscores Oracle’s commitment to improving the

productivity and supporting the needs of the database developer community.

The initial production release of SQL Developer was in March 2006. The latest

release of SQL Developer is release 1.5.1, available from June 2007. This white

paper is an overview of many of the features available in SQL Developer.

Audience

SQL Developer is designed for database developers who spend large portions of

their working days involved in database tasks, possibly also supporting application

developers and DBAs. The role of SQL Developer is to simplify database

development tasks, thus increasing developer productivity.

Key Benefits

Developed in Java, SQL Developer runs on Windows, Linux and Mac OS X.

This is a great advantage to the increasing numbers of developers using alternative

platforms. Multiple platform support also means that users can install SQL

Developer on the Database Server and connect remotely from their desktops,

thus avoiding client server network traffic. Default connectivity to the database is

through the JDBC thin driver, so no Oracle Home is required. To install SQL

Developer simply unzip the downloaded file.

The tool is available for Oracle Database 11g R1, all releases of Oracle Database

10g and Oracle9i Database from version 9.2.0.1. Additionally, SQL Developer is

certified to run against all editions of Oracle Database, including Oracle Database

Oracle SQL Developer for Database Developers Page 4

10g and Oracle Database 11g Enterprise Edition, Standard Edition, Standard

Edition One, and Oracle Database 10g Express Edition.

Architecture

SQL Developer was developed in Java leveraging the Oracle JDeveloper IDE. As

such, it is the first independently branded product based on this IDE. There is a

separate distribution available bundled with JRE 1.5, with an additional tools.jar to

support Windows clients. For Windows clients, users have a choice of not

downloading the bundled distribution and can download the JDK separately.

SQL Developer 1.5.1 is certified to run against JDK 1.5 (5.0) or JDK 6.0 or the

latest updates. Specifically, Windows and non-Windows clients can download and

use the JDK 1.5 (update 6 or later) or JDK 1.6 (update 3 or later).

KEY FEATURES

Managing Connections

SQL Developer allows users to create stored database connections using a simple

dialog. Use these connections to browse the database, create schema objects,

execute and tune ad-hoc SQL statements, run reports against the data dictionary

and create, execute and debug PL/SQL.

Default connectivity to the database is through the JDBC thin driver. Just fill in

the database details in the fields in the Basic tab in the connections dialog.

However, if the environment uses a tnsnames.ora file setup, the aliases in TNS tab

can be used in the connection dialog instead.

Figure 1: Managing Connections

Oracle SQL Developer for Database Developers Page 5

Additional Authentication and Connection Methods

SQL Developer 1.5.1 introduced further connection methods, by introducing the

ability to support LDAP connections. Additional authentication methods of OS

Authentication and Proxy Connections are also available.

Grouping Connections

For large collections of connections, SQL Developer provides the facility of

grouping these into folders. This means that users need not always rely on a

naming convention for all connections, although this is also recommended. Using

SQL Developer users can create a selection of folders and drag one or more

connections into the required folders.

Exporting Connections

For a large number of connections, it is useful to export them to an XML file.

This is especially useful for duplicating connections on other clients. While

importing or exporting, select some or all of the connections.

When creating a new database connection, SQL Developer offers the facility of

saving the user password. While this password is encrypted, it is still a file, which

can be copied and shared, and as such, is open to the usual file system security

risks. You may prefer not have the password stored at all.

Working with the SQL Worksheet

Executing Statements in the SQL Worksheet

Once you have created a database connection, as soon as you connect using that

connection, a SQL Worksheet window is automatically opened.

The SQL Worksheet supports the creation of SQL, PL/SQL and SQL *Plus

commands. You can specify any actions that can be processed

...