Oracle SQL Developer 2.1

5 (1 reviews total)
By Sue Harper
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Getting Started with SQL Developer

About this book

As technology rapidly evolves, many developers are looking for valuable tools to assist them with their daily tasks. When dealing with databases, a clean, easy-to-navigate interface for working and browsing is essential. Oracle SQL Developer is a graphical user interface that makes life much easier by allowing you to browse database objects, run SQL statements and scripts, and create, edit, and debug PL/SQL statements in the most efficient way. It enhances productivity and simplifies your database development tasks. Although the SQL Developer journey looks simple and easy, there are many areas that can go undiscovered, leaving you just scratching the surface.

It’s easy to get started and master this powerful tool with this book to hand. It will provide you with in-depth details about all aspects of using SQL Developer to assist you in your day-to-day database tasks and activities. You will learn to utilize SQL Developer’s extensible environment to support your ongoing needs

This book offers detailed instructions for installing, configuring, and effectively using Oracle SQL Developer. You will learn how to utilize every feature of this development tool and make the most out of it. While none of the tasks are complex, the book progresses from the easy, most commonly used features, such as browsing objects and writing queries in the SQL Worksheet, to the more involved and possibly less frequently used features, such as Tuning and Testing SQL and PL/SQL, and adding User Extensions, and finally to those features used by a smaller more targeted audience, such as Migrations, Oracle APEX, and the Data Modeler. Throughout the book there are tips and suggestions gathered as a result of working with the current SQL Developer user base. This book will also show you how to assess the health of your database with built-in as well as customized reports.

By the end of the book you will be confident in making the best use of SQL Developer, and be able to set up and maintain a productive environment for quick and easy database development.

Publication date:
December 2009
Publisher
Packt
Pages
496
ISBN
9781847196262

 

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

Note

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.

Installing and starting SQL Developer

SQL Developer does not use an installer. All you need to do is unzip the given file into an empty folder, locate, and run the executable.

Note

Do not unzip SQL Developer into an $Oracle_Home folder or an existing SQL Developer install.

Unzipping the file creates an sqldeveloper folder, which includes a selection of sub-folders and files, including the sqldeveloper.exe executable.

If your download does not include the JDK, then you'll be prompted for the full path of the java.exe. Browse to the location of the file and select it. The path should include the full path and executable (for example, C:\Program Files\Java\jdk1.6.0_13\bin\java.exe):

Working with different platforms

Whether you are accessing SQL Developer as part of the Oracle Database 11g installation or as a stand-alone install, there is a selection of executables available to you. These are either platform specific or provide additional detail while running the product.

Microsoft Windows

The first executable you'll find is in the root folder \sqldeveloper. This is the executable more generally used. If you navigate down to \sqldeveloper\bin, there are two additional executables, sqldeveloper.exe and sqldeveloperW.exe. The latter is the same as the executable in the root folder. Use either of these for running SQL Developer.

The additional executable is often used for debugging purposes. Use \sqldeveloper\bin\sqldeveloper.exe to invoke SQL Developer and a separate console window which displays additional Java messages. You can use these messages when encountering errors in the product and if you want to log an issue with Oracle Support.

Note

Oracle SQL Developer

Three steps to getting started on Microsoft Windows:

Download: Download the full file, with JDK, from the Oracle Technology Network web site.

Unzip: Unzip the file to an empty directory.

Double-click: Double-click on the \sqldeveloper\ sqldeveloper.exe file.

Alternative platforms

Microsoft Windows is the predominant platform used by SQL Developer users. There is a steadily growing audience for Linux and Max OS X. As neither of these platform downloads include the JDK, you need to first access, download, and install the JDK. On starting either Linux or the Mac OS, you'll be prompted for the full path of the JDK as described.

Mac OS X

Download the file specific to Mac OS X and double-click to unzip the file. This creates an icon for SQL Developer on your desktop. Double-click to run the application.

Linux

Use the Linux rpm command to install SQL Developer. For example, your command might look like this:

rpm -Uhv sqldeveloper-1.5.54.40-1.noarch.rpm

In the same way that unzip creates an sqldeveloper folder, with sub-folders and files, the rpm command creates an sqldeveloper folder, complete with files and sub-folders. Switch to this new folder and run the sqldeveloper.sh executable.

Migrating settings from a previous release

On the initial startup of any release of SQL Developer, you may be asked one or two questions. The first is the location of the Java executable of the JDK as discussed. If you have installed the full release with the JDK, this question is skipped. The second question is if you want to migrate any preferences from a previous release. Regardless of whether this is the first SQL Developer install on the machine or not, the first time you invoke SQL Developer, you are offered the choice of migrating your settings. You can migrate settings of any release from SQL Developer 1.5 and above. By default, the utility looks for the latest previous installation of the software.

If you want to migrate from a different installation, select the Show All Installations button (seen above). This displays a list of all SQL Developer installations that have the system folder in the Documents and Settings system folder (for example, C:\Documents and Settings\<your_user>\Application Data\SQL Developer\system1.5.1.54.40) and includes releases from SQL Developer 1.5 and above. For releases prior to SQL Developer 1.5, the system folder was created within the SQL Developer install (for example, D:\SQLDeveloper\Builds\1.2.1\1.2.1.3213\sqldeveloper\sqldeveloper\system).

Maintaining your environment

Once you have SQL Developer installed, it is helpful to know about the environmental settings and some of the files that are created when you start the product. Knowing about the version you have installed is important if only to be able to identify this when asking questions on the forum, or when contacting Oracle Support.

Verifying the current release

To verify the SQL Developer release you have, select the Help | About menu once you start SQL Developer or JDeveloper. In the dialog invoked, select the Extensions tab and find the Oracle SQL Developer extension, as shown in the next screenshot. This will match the build number on the download site if you have the latest release. The screenshot shows a number of the extensions that make up SQL Developer. If your dialog does not show the Version or Status columns, you can select the column headers to resize the visible columns and bring the others into focus.

Using Check for Updates

SQL Developer offers a built-in patching and extensions utility, known as Check for Updates. Check for Updates is used to release:

  • SQL Developer extensions

  • General Oracle extensions

  • Minor patches

  • Third-party tools required by SQL Developer, such as the non-Oracle database drivers

  • Third-party extensions

You can control whether Check for Updates warns you about new updates using the Tools | Preferences menu. Select Extensions and then select Automatically Check for Updates. For SQL Developer extensions, if you already have SQL Developer installed and you're not secured by a firewall, you'll be alerted about new updates. You need not use the utility to get the updates, but you'll be aware of the latest release from the alert. For all other extensions, you need to start Check for Updates to see what's available. To do this, select Help | Check for Updates. In either situation, just follow the dialog to find the updates you require.

You can initially elect to see just the third-party updates, or all updates available, by selecting all options, as shown in the following screenshot:

The database drivers for some of the non-Oracle databases are located in Third Party SQL Developer Extensions. The Third Party update center also includes a selection of customer developed SQL Developer extensions. The customer extensions are developed, supported, and updated by the customer involved, and are not tested, certified, or supported by Oracle.

As with all software downloads from the Internet, you are required to read and accept the license agreements. The Check for Updates utility directs you to the appropriate licenses, before downloading the software. If the updates are from Oracle, you will need to provide your Oracle Technology Network sign-on details.

Check for Updates is only used to apply patches to your install. Starting with SQL Developer 1.5.1, the team released patches which are applied to upgrade the product in place. These patches fixed one or two bugs and did not constitute a new download, or even a full install. For all other releases, you need to do a full install as described earlier.

Managing the system folder and other files

SQL Developer maintains a series of files as you work with the product. These files are created and stored in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. On Linux, these files are all stored in a folder ~/.sqldeveloper/.

Deleting this folder is equivalent to returning a SQL Developer installation to its factory settings. The files at this highest level are:

  • CodeTemplate.xml created as you add your own user defined code templates

  • UserReports.xml created as you add your own user defined reports

  • SqlHistory.xml created as you execute SQL and PL/SQL commands in the SQL Worksheet

  • UserSnippets.xml created as you add your own snippets

These files are used by each of the SQL Developer installations you have. For example, you may elect to have the latest installation in addition to a number of earlier releases. Having several different releases of SQL Developer on one machine is acceptable, as the installations have no impact on each other, except that they do share these files.

Once you have started SQL Developer, a folder with sub-folders and files is created in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. The top-level folder is labeled systemx.x.x.x.x (for example system1.5.0.54.40). The systemx.x.x.x.x folder contains all of the other preferences and the settings that pertain to the specific release in use. In this case, deleting the system folder is almost equivalent to resetting SQL Developer to its factory settings, except that any user defined reports, SQL history, and code templates are not lost.

Sharing preferences

Preferences are set for your local environment and are therefore not shared globally between teams. However, you can export the SQL Formatter preferences set. This allows you to share the settings between team members and ensure that you all code to the same settings. To export your SQL Formatter settings, select Tools | Preferences and expand the Database node in the tree. Select SQL Formatter, you can now export or import previous saved settings.

Alternative installations of SQL Developer

We have been discussing the installation and management of the independent release of SQL Developer available on the Oracle Technology Network. SQL Developer is also available as part of the Oracle Database and Oracle JDeveloper installations.

Oracle JDeveloper

Most of SQL Developer is integrated into Oracle JDeveloper, which means you need to install JDeveloper to access and use the SQL Developer components. Having SQL Developer as part of JDeveloper means that, if you are building Java applications and working with the Fusion Middleware platform, you can access and work with the Oracle Database without an additional install of SQL Developer. JDeveloper does not consume all of the extensions for SQL Developer (for example, extensions like Migrations and Versioning are not included).

Note

Oracle JDeveloper 11g includes SQL Developer 1.5.6.

Oracle Database 11g

SQL Developer is also shipped with the Oracle Database. Initially, Oracle Database 11g Release 1. SQL Developer is installed by default when you install the database. Once the installation is complete, locate the sqldeveloper directory (for example, \product\11.1.0\db_1\sqldeveloper\sqldeveloper.exe) to start SQL Developer.

Be aware that Oracle database releases are less frequent than those of SQL Developer, which, by its nature and size, allows for more frequent updates. This means the version of SQL Developer shipped with the database may not be the most current release. Oracle Database 11g Release 2 is shipped with SQL Developer 1.5.5. All examples in this text are using SQL Developer 2.1. You may also update your database version less frequently than a client tool.

To upgrade the SQL Developer installation in Oracle Database 11g Release 1, you should do a full new install. As with other installs, create a new folder and unzip the latest download.

Note

Oracle Database 11g Release 1 ships with SQL Developer 1.1.3

Oracle Database 11g Release 2 ships with SQL Developer 1.5.5

Troubleshooting

It seems ominous to provide a section on troubleshooting at the start of a book! If you accept that software can get in a tangle sometimes, either if you use the product as it's not designed, or perhaps include extensions that you'd prefer not to have and the product is no longer behaving as expected, then a few hints on how to escape that tangle can be useful.

Removing extensions

If you have created your own extensions, or have downloaded and installed other extensions that you no longer require, then invoke the preferences, using the menu Tools | Preferences and select Extensions from the tree. Here you see that SQL Developer includes a number of default extensions, such as the Oracle TimesTen extension. In addition, any extension that you have included is listed here. You can deselect extensions here and the product will no longer access them. This does not delete the files installed for the extension. You will need to manually delete any files downloaded for that to happen. However, it does mean that you can restart the product and see if the extension is the root of the problem.

Resetting shortcut keys

Some users find that their keyboard shortcuts no longer work as expected. In this circumstance, you can select the menu Tools | Preferences, and then select Shortcut Keys from the tree. Click on the More Actions drop-down list and select Load Keyboard Scheme…, as shown in the following screenshot. Select Default from the dialog to reset the keyboard accelerators to the shipped settings. This also replaces any settings you have added.

Note

In releases prior to SQL Developer 2.1, the Shortcut Keys are called Accelerators. In these releases, to reset the keys, select Load Preset.

Reset the environment, do not reinstall the product

When things go wrong, users sometimes resort to deleting and reinstalling a product. This may even require downloading the files again. This is time consuming, and in the case of SQL Developer, not necessary. Assuming you have not edited any of the .jar files (it's been known to happen and not legally permitted), you can reset the product to the shipped factory settings by deleting the system folder. Before you delete the system folder, export your connections and shut down SQL Developer.

Note

Export Connections: To export your connections, select Connections, right-click and select Export Connections. Save the file to a new location.

When troubleshooting, deleting the system folder is useful. However, by deleting this folder you are also deleting all of the changes made to the preferences, your connections, and any layout changes you have made. Therefore, it is recommended that you delete the folder as a last resort, and not as a standard approach to troubleshooting.

Note

Reset to factory settings

For Microsoft Windows, delete the \Documents and Settings\<your_user>\Application Data\SQL Developer folder to reset SQL Developer to the shipped factory settings.

For Linux, remove the ~.sqldeveloper folder and on the Mac, remove the ~/Library/Application Support/SQL Developer folder.

In addition to deleting all of the preferences set and connections created, this action also deletes user-defined reports, your SQL history, and any code templates and snippets you have created. In general, delete the lower level system folder for a less drastic reset.

 

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:

Note

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.

Installing and starting SQL Developer

SQL Developer does not use an installer. All you need to do is unzip the given file into an empty folder, locate, and run the executable.

Note

Do not unzip SQL Developer into an $Oracle_Home folder or an existing SQL Developer install.

Unzipping the file creates an sqldeveloper folder, which includes a selection of sub-folders and files, including the sqldeveloper.exe executable.

If your download does not include the JDK, then you'll be prompted for the full path of the java.exe. Browse to the location of the file and select it. The path should include the full path and executable (for example, C:\Program Files\Java\jdk1.6.0_13\bin\java.exe):

Working with different platforms

Whether you are accessing SQL Developer as part of the Oracle Database 11g installation or as a stand-alone install, there is a selection of executables available to you. These are either platform specific or provide additional detail while running the product.

Microsoft Windows

The first executable you'll find is in the root folder \sqldeveloper. This is the executable more generally used. If you navigate down to \sqldeveloper\bin, there are two additional executables, sqldeveloper.exe and sqldeveloperW.exe. The latter is the same as the executable in the root folder. Use either of these for running SQL Developer.

The additional executable is often used for debugging purposes. Use \sqldeveloper\bin\sqldeveloper.exe to invoke SQL Developer and a separate console window which displays additional Java messages. You can use these messages when encountering errors in the product and if you want to log an issue with Oracle Support.

Note

Oracle SQL Developer

Three steps to getting started on Microsoft Windows:

Download: Download the full file, with JDK, from the Oracle Technology Network web site.

Unzip: Unzip the file to an empty directory.

Double-click: Double-click on the \sqldeveloper\ sqldeveloper.exe file.

Alternative platforms

Microsoft Windows is the predominant platform used by SQL Developer users. There is a steadily growing audience for Linux and Max OS X. As neither of these platform downloads include the JDK, you need to first access, download, and install the JDK. On starting either Linux or the Mac OS, you'll be prompted for the full path of the JDK as described.

Mac OS X

Download the file specific to Mac OS X and double-click to unzip the file. This creates an icon for SQL Developer on your desktop. Double-click to run the application.

Linux

Use the Linux rpm command to install SQL Developer. For example, your command might look like this:

rpm -Uhv sqldeveloper-1.5.54.40-1.noarch.rpm

In the same way that unzip creates an sqldeveloper folder, with sub-folders and files, the rpm command creates an sqldeveloper folder, complete with files and sub-folders. Switch to this new folder and run the sqldeveloper.sh executable.

Migrating settings from a previous release

On the initial startup of any release of SQL Developer, you may be asked one or two questions. The first is the location of the Java executable of the JDK as discussed. If you have installed the full release with the JDK, this question is skipped. The second question is if you want to migrate any preferences from a previous release. Regardless of whether this is the first SQL Developer install on the machine or not, the first time you invoke SQL Developer, you are offered the choice of migrating your settings. You can migrate settings of any release from SQL Developer 1.5 and above. By default, the utility looks for the latest previous installation of the software.

If you want to migrate from a different installation, select the Show All Installations button (seen above). This displays a list of all SQL Developer installations that have the system folder in the Documents and Settings system folder (for example, C:\Documents and Settings\<your_user>\Application Data\SQL Developer\system1.5.1.54.40) and includes releases from SQL Developer 1.5 and above. For releases prior to SQL Developer 1.5, the system folder was created within the SQL Developer install (for example, D:\SQLDeveloper\Builds\1.2.1\1.2.1.3213\sqldeveloper\sqldeveloper\system).

Maintaining your environment

Once you have SQL Developer installed, it is helpful to know about the environmental settings and some of the files that are created when you start the product. Knowing about the version you have installed is important if only to be able to identify this when asking questions on the forum, or when contacting Oracle Support.

Verifying the current release

To verify the SQL Developer release you have, select the Help | About menu once you start SQL Developer or JDeveloper. In the dialog invoked, select the Extensions tab and find the Oracle SQL Developer extension, as shown in the next screenshot. This will match the build number on the download site if you have the latest release. The screenshot shows a number of the extensions that make up SQL Developer. If your dialog does not show the Version or Status columns, you can select the column headers to resize the visible columns and bring the others into focus.

Using Check for Updates

SQL Developer offers a built-in patching and extensions utility, known as Check for Updates. Check for Updates is used to release:

  • SQL Developer extensions

  • General Oracle extensions

  • Minor patches

  • Third-party tools required by SQL Developer, such as the non-Oracle database drivers

  • Third-party extensions

You can control whether Check for Updates warns you about new updates using the Tools | Preferences menu. Select Extensions and then select Automatically Check for Updates. For SQL Developer extensions, if you already have SQL Developer installed and you're not secured by a firewall, you'll be alerted about new updates. You need not use the utility to get the updates, but you'll be aware of the latest release from the alert. For all other extensions, you need to start Check for Updates to see what's available. To do this, select Help | Check for Updates. In either situation, just follow the dialog to find the updates you require.

You can initially elect to see just the third-party updates, or all updates available, by selecting all options, as shown in the following screenshot:

The database drivers for some of the non-Oracle databases are located in Third Party SQL Developer Extensions. The Third Party update center also includes a selection of customer developed SQL Developer extensions. The customer extensions are developed, supported, and updated by the customer involved, and are not tested, certified, or supported by Oracle.

As with all software downloads from the Internet, you are required to read and accept the license agreements. The Check for Updates utility directs you to the appropriate licenses, before downloading the software. If the updates are from Oracle, you will need to provide your Oracle Technology Network sign-on details.

Check for Updates is only used to apply patches to your install. Starting with SQL Developer 1.5.1, the team released patches which are applied to upgrade the product in place. These patches fixed one or two bugs and did not constitute a new download, or even a full install. For all other releases, you need to do a full install as described earlier.

Managing the system folder and other files

SQL Developer maintains a series of files as you work with the product. These files are created and stored in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. On Linux, these files are all stored in a folder ~/.sqldeveloper/.

Deleting this folder is equivalent to returning a SQL Developer installation to its factory settings. The files at this highest level are:

  • CodeTemplate.xml created as you add your own user defined code templates

  • UserReports.xml created as you add your own user defined reports

  • SqlHistory.xml created as you execute SQL and PL/SQL commands in the SQL Worksheet

  • UserSnippets.xml created as you add your own snippets

These files are used by each of the SQL Developer installations you have. For example, you may elect to have the latest installation in addition to a number of earlier releases. Having several different releases of SQL Developer on one machine is acceptable, as the installations have no impact on each other, except that they do share these files.

Once you have started SQL Developer, a folder with sub-folders and files is created in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. The top-level folder is labeled systemx.x.x.x.x (for example system1.5.0.54.40). The systemx.x.x.x.x folder contains all of the other preferences and the settings that pertain to the specific release in use. In this case, deleting the system folder is almost equivalent to resetting SQL Developer to its factory settings, except that any user defined reports, SQL history, and code templates are not lost.

Sharing preferences

Preferences are set for your local environment and are therefore not shared globally between teams. However, you can export the SQL Formatter preferences set. This allows you to share the settings between team members and ensure that you all code to the same settings. To export your SQL Formatter settings, select Tools | Preferences and expand the Database node in the tree. Select SQL Formatter, you can now export or import previous saved settings.

Alternative installations of SQL Developer

We have been discussing the installation and management of the independent release of SQL Developer available on the Oracle Technology Network. SQL Developer is also available as part of the Oracle Database and Oracle JDeveloper installations.

Oracle JDeveloper

Most of SQL Developer is integrated into Oracle JDeveloper, which means you need to install JDeveloper to access and use the SQL Developer components. Having SQL Developer as part of JDeveloper means that, if you are building Java applications and working with the Fusion Middleware platform, you can access and work with the Oracle Database without an additional install of SQL Developer. JDeveloper does not consume all of the extensions for SQL Developer (for example, extensions like Migrations and Versioning are not included).

Note

Oracle JDeveloper 11g includes SQL Developer 1.5.6.

Oracle Database 11g

SQL Developer is also shipped with the Oracle Database. Initially, Oracle Database 11g Release 1. SQL Developer is installed by default when you install the database. Once the installation is complete, locate the sqldeveloper directory (for example, \product\11.1.0\db_1\sqldeveloper\sqldeveloper.exe) to start SQL Developer.

Be aware that Oracle database releases are less frequent than those of SQL Developer, which, by its nature and size, allows for more frequent updates. This means the version of SQL Developer shipped with the database may not be the most current release. Oracle Database 11g Release 2 is shipped with SQL Developer 1.5.5. All examples in this text are using SQL Developer 2.1. You may also update your database version less frequently than a client tool.

To upgrade the SQL Developer installation in Oracle Database 11g Release 1, you should do a full new install. As with other installs, create a new folder and unzip the latest download.

Note

Oracle Database 11g Release 1 ships with SQL Developer 1.1.3

Oracle Database 11g Release 2 ships with SQL Developer 1.5.5

Troubleshooting

It seems ominous to provide a section on troubleshooting at the start of a book! If you accept that software can get in a tangle sometimes, either if you use the product as it's not designed, or perhaps include extensions that you'd prefer not to have and the product is no longer behaving as expected, then a few hints on how to escape that tangle can be useful.

Removing extensions

If you have created your own extensions, or have downloaded and installed other extensions that you no longer require, then invoke the preferences, using the menu Tools | Preferences and select Extensions from the tree. Here you see that SQL Developer includes a number of default extensions, such as the Oracle TimesTen extension. In addition, any extension that you have included is listed here. You can deselect extensions here and the product will no longer access them. This does not delete the files installed for the extension. You will need to manually delete any files downloaded for that to happen. However, it does mean that you can restart the product and see if the extension is the root of the problem.

Resetting shortcut keys

Some users find that their keyboard shortcuts no longer work as expected. In this circumstance, you can select the menu Tools | Preferences, and then select Shortcut Keys from the tree. Click on the More Actions drop-down list and select Load Keyboard Scheme…, as shown in the following screenshot. Select Default from the dialog to reset the keyboard accelerators to the shipped settings. This also replaces any settings you have added.

Note

In releases prior to SQL Developer 2.1, the Shortcut Keys are called Accelerators. In these releases, to reset the keys, select Load Preset.

Reset the environment, do not reinstall the product

When things go wrong, users sometimes resort to deleting and reinstalling a product. This may even require downloading the files again. This is time consuming, and in the case of SQL Developer, not necessary. Assuming you have not edited any of the .jar files (it's been known to happen and not legally permitted), you can reset the product to the shipped factory settings by deleting the system folder. Before you delete the system folder, export your connections and shut down SQL Developer.

Note

Export Connections: To export your connections, select Connections, right-click and select Export Connections. Save the file to a new location.

When troubleshooting, deleting the system folder is useful. However, by deleting this folder you are also deleting all of the changes made to the preferences, your connections, and any layout changes you have made. Therefore, it is recommended that you delete the folder as a last resort, and not as a standard approach to troubleshooting.

Note

Reset to factory settings

For Microsoft Windows, delete the \Documents and Settings\<your_user>\Application Data\SQL Developer folder to reset SQL Developer to the shipped factory settings.

For Linux, remove the ~.sqldeveloper folder and on the Mac, remove the ~/Library/Application Support/SQL Developer folder.

In addition to deleting all of the preferences set and connections created, this action also deletes user-defined reports, your SQL history, and any code templates and snippets you have created. In general, delete the lower level system folder for a less drastic reset.

 

A quick overview


Let's start with a walk-through of the product. This book is all about SQL Developer, using the product, and getting to know it. You may well ask yourself why there is a need for a book if we can walk through the product in twenty minutes or less. By spending a little time dipping into a number of areas of the product, you can start laying down a map of how the pieces connect and provide a base that you can drill down into later.

Sample schemas

To follow the examples in the book, you need access to SYSTEM and the shipped sample schemas, HR, OE, SH, PM, and IX available in Oracle Database 9i, 10g, or 11g. Specifically, this book uses the sample schemas shipped with Oracle Database 11g.

There are two ways to install the sample schema. The first way is when you install the database. You can elect to have the sample schema installed at that point.

Second, if you have not installed these, then you can locate the sample schema in the $ORACLE_HOME/demo/schema folder and follow the instructions on installing them using the Oracle online documentation. Not all of these schemas are available for Oracle Express Edition. In this chapter, we use SYSTEM to verify the HR schema is unlocked, and then we use the HR sample schema, which is available in Oracle Express Edition.

Creating your first connection

To complete this quick walk-through, you need to know the username and password of the SYSTEM user. You also need to know the location of the database, whether this is the machine name or the IP address, and the database SID.

To begin, start SQL Developer. The very first time you start SQL Developer, you'll be asked if you want to migrate from a previous version. Select No and allow the tool to start up.

The first thing you need to do after you have started SQL Developer for the first time is to create your initial connections.

Create a connection for SYSTEM:

  1. 1. Select Connections, right-click and select New Connection. This invokes the New Database Connection dialog. You can edit and control all of the connection details using this dialog.

  2. 2. Complete the details, as seen in the following screenshot, relevant to your environment.

  3. 3. Click on Test to ensure you have the connection details correct and click on Connect.

You are now connected as SYSTEM. Use this connection to verify your other users, by continuing as follows:

  1. 1. Select the new connection you have created, expand the node, and scroll down to Other Users.

  2. 2. Expand Other Users and find the user HR. Right-click and select Edit User. Verify that the account for HR is unlocked and the Password has not expired, that is, the properties Account is Locked and Password Expired are deselected. If either of these is selected, deselect them. You can change the password for HR at this point too. It's good practice to modify the passwords of the shipped sample schemas once you have unlocked them.

Now you are really ready to begin!

  1. 1. Once again, select Connections, right-click and select New Connection.

  2. 2. Give the connection a name (for example, HR_11g).

  3. 3. Provide the Username (HR) and a Password. If you are working on Oracle Database 11g, be aware that passwords are now case sensitive.

  4. 4. Select the Save Password checkbox. This makes life easy while you are working with SQL Developer. Passwords are stored in an encrypted file. However, you should always be aware of saving passwords and possible security implications this may have.

  5. 5. Use the Basic connection. This requires no more detail than the location of the database and the SID, details you have.

  6. 6. Click on Test to test the connection.

  7. 7. Click on Connect.

Using basic commands in the SQL Worksheet

As soon as you connect to a user, SQL Developer opens an SQL Worksheet. You may have started working with Oracle using the SQL*Plus command line, or even the GUI window. Either way, you'd start with a selection of SQL*Plus and SQL commands.

Enter the following into the SQL Worksheet:

DESC DEPARTMENTS
SELECT * FROM DEPARTMENTS;

Press the F5 key (or use the Run Script button).

The output of both commands appears in the Script Output tab, which appears below the SQL Worksheet (as seen in the previous screenshot). Both commands are handled by a few simple clicks of the mouse in SQL Developer.

Select and expand the HR_11g connection in the Connections navigator. Expand the Tables node and select DEPARTMENTS.

The DEPARTMENTS tab now opens, displaying a list of the column names and details. These are the same details as given by the DESC (describe) SQL*Plus command that you entered in the SQL Worksheet. It also provides additional detail, such as the Primary Key and column comments.

Select the Data tab and notice that you now see the output from your second command. These two tabs are included with a number of other tabs, each with additional details about the DEPARTMENTS table. You would need to write a number of SQL queries in order to get the additional detail from the data dictionary if you were working in SQL*Plus.

Select the EMPLOYEES table. Notice that the new table, EMPLOYEES, immediately replaces the previous DEPARTMENTS table with its details. Select the Triggers tab, and select one of the triggers. The trigger and related trigger detail is displayed in a master-detail window:

Browsing and updating data

Return to the EMPLOYEES data by again selecting the Data tab. The data grid that is displayed provides a variety of options. To get started with the data grid, double-click on an item or field, such as the name of one of the employees, and change it. Tab out of the field and notice that the change is applied to the data grid and an asterisk (*) flags the record. Commit and Rollback buttons are available to send the change to the database, or to undo your action. Roll back the changes.

Once again you get feedback, this time in the Data Editor log, as shown in the following screenshot:

Running reports

Select the Reports navigator and expand the Data Dictionary Reports node. Expand the Table node and review the available reports. Expand Constraints and select the Unique Constraints report. As you select the report, a dialog displays requesting the Connection name. Select the connection you created, HR_11g, and click on OK. An Enter Bind Values dialog now appears, requesting the table name as an input parameter. Click on Apply to accept the default, which in this case, means all tables:

Note

Run the same report for any user by selecting the Connections drop-down list on the right-hand side.

 

Navigating around SQL Developer


SQL Developer has a selection of windows, navigators, and tabs. On start-up, you are presented with the main navigator toolbars and menus:

The two main navigators: Connections and Reports, are presented in a tabbed window. These and other navigators, such as the Versioning Navigator, are available through the main View menu. You can also open windows such as Snippets, Recent Objects, and Find DB Objects using the View menu.

Note

Any navigators that you open during a session, and that are still open when you close the product, are automatically opened when you restart the product.

Managing SQL Developer windows

With the exception of the SQL Worksheet and its associated tabs, all of the main tabbed dialogs can be minimized or maximized and accessed while docked or undocked. These menu controls are available through context menus in the tabs:

You can rearrange tabbed windows by selecting and dragging the tab into place. Once any window is minimized, roll your mouse over the minimized tab to display a floating window that stays active while your mouse lies over it and rolls back into place when you move off. This is very useful when working with temporary windows such as Snippets and Find DB Object. The following screenshot shows the floating window for the Snippets dialog. If you roll the mouse over the area, you can work in the window (for example, navigating about until you have located the snippet of code you are after, and then drag the code onto the worksheet). The window will minimize out of the way once you have moved off it.

You can undock the floating window, move it off to one side, and keep it undocked while you work with the SQL Worksheet. In a dual-monitor setup, you can drag the floating window onto one monitor, while working with the SQL Worksheet on the other monitor.

Tiling windows

Once you start working with connections, you have more windows and tabs to deal with, especially if you have more than one connection created. Select the HR_11g connection created in the previous section, expand the connection and Tables node, and select EMPLOYEES. In the table definition window, select the pin button, as shown below, to freeze the view.

Now, select the DEPARTMENTS table. A second table definition window opens to display the details from the new table. Select the DEPARTMENTS tab and drag it down to the lower portion of the screen. Notice the shape of the dragged object change as you drag it slightly to the left, to the center, and the lower portion of the window. Each of the shapes represents a different layout position. Release the mouse to secure the new position. The screenshots, which follow, display two of the available positions:

Splitting documents

When you tile windows, you can compare the details of two tables. However, as each table has a selection of tabs, it's useful to be able to review details in the tabs without having to switch back and forth between tabs. As is true for other layout features, you can split the document using a menu, or by drag-and-drop. Each of the object definitions tabbed displays has a drag bar on the top and bottom right that you can select and drag to split the window horizontally, or vertically:

Maximizing detail

Almost all of the tabs in SQL Developer will maximize when double-clicked. There are a few that do not follow this rule, such as the tabs related to the SQL Worksheet. In general, this works for top-level tabs, which is any tab you can undock and move about, and not for secondary tabs. To maximize a tab, double-click on the tab. A second double-click will reverse the process.

Note

Double-click on the tab to maximize a top-level tab. Double-click again to revert to the previous layout.

Resetting the window layout

If you move your windows about a great deal, you may find that you want to get things back to the default settings.

The example in the following screenshot displays the standard docked Connections and Reports windows to the left. We have also opened the Versioning Navigator, which by default docks below the connections. We have also docked the Snippets window to the right. These windows fill the columns to the left and right, leaving a central window for the editors and log files.

The layout is controlled by the window layout set in the preferences. Select Tools | Preferences, under the Environment node in the tree select Dockable Windows. The default layout, and the one that matches the example in the previous screenshot, is shown in the following screenshot:

Each of the little curved arrows on the diagram is clickable, and as such controls the positioning of the windows. Clicking on the arrow extends or contracts the area taken up by the docked window.

In our example, and in the default SQL Developer environment, there is no full-docked window across the top of the screen. However, if you drag a window into the docked position below the main tool bar, it would stretch across the screen, as shown in the following screenshot:

If you find your windows are in a muddle, first verify that the Dockable Windows layout is what you want, and then drag the various dockable windows back into place. Some suggestions on the SQL Developer forum are to remove the system folder (it works, but that's an extreme solution).

 

Finding more help


SQL Developer has a site on the Oracle Technology Network, http://www.oracle.com/technology/products/database/sql_developer/index.html. This provides links to current and past magazine articles, white papers, and team blogs. It also has links to brief product demonstrations and longer hands-on exercises.

There is an active user forum on OTN, http://forums.oracle.com/forums/forum.jspa?forumID=260, which is monitored by the development team and end users.

The SQL Developer Exchange, http://sqldeveloper.oracle.com, is a site where anyone using SQL Developer can log feature requests and vote on other requests already posted. In addition to posting feature requests, the site hosts reports and code snippets.

 

Summary


You've started and should now have SQL Developer installed. You should have a few connections created and an initial idea of how to navigate around the product. You are now set to learn a lot more about SQL Developer. From here you can dip into different chapters, focusing on the areas you're most interested in.

In the next chapter, we'll show you how to browse different types of objects, and use SQL Developer to look at them in greater detail. We'll review the different editors and dialogs available and how you can manage what you see using preferences. We'll also show you the different ways you can create objects and how to manipulate the data.

About the Author

  • Sue Harper

    Sue Harper was a senior school mathematics and science teacher by profession. It was this teaching qualification that opened the door to Oracle in South Africa, where Sue started as a SQL, PL/SQL, SQL Forms, and Reports instructor in 1992. Before long she'd added Oracle Designer to her repertoire and soon left for the UK, to join the Designer curriculum development team. Sue was a course writer and then product manager for Oracle Designer for many years, traveling extensively meeting customers and training Oracle staff and instructors. Sue was later product manager for Oracle JDeveloper working with database and modeling tools in that product. Sue is currently product manager for Oracle SQL Developer, SQL Developer Data Modeler, and SQL Developer Migrations. Based at home, Sue works with her team, scattered around the world and customers, running web-based training sessions or helping with individual queries. She frequently attends conferences where she presents SQL Developer material and works with customers on their queries.

    Sue lives in West London, where she enjoys walking with her camera and her dog in the extensive local parks and further afield throughout the UK.

    Browse publications by this author

Latest Reviews

(1 reviews total)
very simple lucid language in explanation on topic and subject.
Book Title
Access this book, plus 7,500 other titles for FREE
Access now