23 Getting Started with Working with Databases

This chapter describes how to get started using JDeveloper to work with databases.

This chapter includes the following sections:

23.1 About Working with Databases

JDeveloper enables you to work with Oracle and non-Oracle databases directly, and to design, create, and edit databases by working with offline database definitions.

Refer to the following documentation to quickly get started with using Oracle databases in JDeveloper:

23.1.1 Connecting to and Working with Databases

Usually you start working with a database by creating a connection to it, or by importing an existing connection. JDeveloper helps you quickly to create connections to Oracle databases, and you can also connect to and work with a number of non-Oracle databases. Once you have a database connection you can search for database objects in the Databases window, or use the search tools to find specific objects, or compare databases and their contents. You can also edit data and import and export data, and you can create reports about the database and objects in it.

23.1.2 Designing Databases

You can work directly with databases through a database connection using the integrated tools in JDeveloper which include SQL Worksheet and the database object editors. Alternatively, you can create an offline database and working either in the Applications window or the database modeler you can work with offline database definitions to model the database and then generate the results to a database through a database connection.

Database connections can be listed in the Applications window or Databases window, where they are available to applications you are working on, or in the Resources window, where they are available for reuse in other applications.

Once you have a database connection, you can:

  • Browse and search databases for specific objects.

  • Produce reports about databases and their contents.

  • Import and export data.

  • Copy, compare and export databases.

You can work with offline databases, which you can model on the database modeler or work with in the Applications window.

You can create, edit, and drop objects in a database or in an offline database.

You can write and execute Java programs using JDBC that access Oracle and non-Oracle databases.

If you are new to using databases with JDeveloper, one of the easiest ways to get started is to try out Oracle Database 11g Express Edition (Oracle Database XE).

23.2 Getting Started With Oracle Database Express Edition

If you are new to using databases with JDeveloper, one of the easiest ways to get started is to try out Oracle Database 11g Express Edition (Oracle Database XE). Oracle Database XE is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base. It is free to develop, deploy, and distribute; fast to download; and simple to administer. You can download it from Getting Started: Oracle Database Express Edition (XE), which is available at http://www.oracle.com/technetwork/database/express-edition/overview/index.html

After you have downloaded and installed Oracle Database XE, use the Oracle Database Express Edition and Oracle Application Express documentation to create and administer users, and unlock the sample user, HR. You may want to grant additional privileges, for example to create tables and materialized views. Now you can create a database connection from JDeveloper to the sample user. How?

In the Create Database Connection dialog, use the following values. Leave blank any fields that are not mentioned.

Table 23-1 Connection details for Oracle Database Express Edition

Field Value

Create Connection In

Choose Application resources. The connection will be displayed in the Applications window, under Application Resources.

Connection Name

Enter a meaningful name for this connection.

Connection Type

Oracle (JDBC) (default).


HR to use the sample user. If you have created a new database user, enter the name of that user.


Enter the password you entered when you unlocked the sample user or created a new user.

Save Password

Selected (default).


thin (default)

Host Name

When Oracle Database XE is installed on the local system use the default of localhost or Otherwise enter the IP address or resolvable hostname of the machine where it is installed.


1521 (default)


XE (default)

Click Test Connection at the bottom of the dialog. Success! indicates that you have a connection to the database. If you get any other message, check that you have entered the values above correctly, and check that the Oracle Database XE has started.

23.3 How to Manage Database Preferences and Properties

There are a number of preferences that allows you to control how to use the database functionality in JDeveloper. These are available in the Preferences dialog, available from the Tools menu:

Table 23-2

Node in Preferences Dialog Values that can be set


Choose not to have date and time default values validated.

Set the default path for export DDL files.

Database: Advanced

Set options such as the SQL array fetch size and display options for null values.

Database: Autotrace/Explain Plan

Specify the information to be displayed on the Autotrace and Explain Plan pages in the SQL Worksheet.

Database: Drag and Drop

Specify the type of SQL statement created in the SQL Worksheet when you drag an object from the Databases window into the SQL Worksheet.

Database: Licensing

Some JDeveloper features require that licenses for specific Oracle Database options be in effect for the database connection that will use the feature. This page enables you to specify, for each defined connection, whether the database has the Oracle Change Management Pack, the Oracle Tuning Pack, and the Oracle Diagnostics Pack.

For each cell in this display (combination of license and connection), the value can be true (checked box), false (cleared box), or unspecified (solid-filled box).

If an option is specified as true for a connection in this pane, you will not be prompted with a message about the option being required when you use that connection for a feature that requires the option.

Database: Navigation Filter

Specify the type of SQL statement created in the SQL Worksheet when you drag an object from the Databases window into the SQL Worksheet.

Control the types of objects that appear in the Databases window for connections to Oracle and third-party databases.

Select Enable Navigation Tree Filtering to choose the tab for the database type you want. For each type you can select the types of objects to appear in the hierarchy for connections to that type of database.

Database: JDBC Driver Options

Register and manage JDBC drivers for the BI JDBC driver, and the WebLogic JDBC drivers for DB2, Informix, SQL Server and Sybase.

Database: NLS

Specify globalization support parameters, such as the language, territory, sort preference, and date format.

Database: ObjectViewer

Specify whether to freeze object viewer windows, and display options for the output.

Database: PL/SQL Compiler

Specify options for compilation of PL/SQL subprograms.

Database: Reports

Choose that database reports in JDeveloper are closed when the database is disconnected.

Select the limit for the number of rows for a chart. The default is 1,000.

Database: SQL*Plus

Set the path to the SQL*Plus command line tool.

Database: SQL Editor Code Templates

View, add, and remove templates for editing SQL and PL/SQL code. Code templates assist you in writing code more quickly and efficiently by inserting text for commonly used statements.

Database: SQL Formatter

Allows you to control how statements in the SQL Worksheet are formatted.

Database: User Defined Extensions

(Not used by JDeveloper.)

Database: Utilities

Provides default values for utility wizards and editors

Database: Worksheet

Specify options for the SQL Worksheet.

Diagrams: Database

Set preferences that control how diagrams are displayed.

To manage database preferences in the Preferences dialog:

  1. Choose Tools > Preferences.

  2. From the Preferences page, select the page you want. For more information at any time, press F1 or click Help from within the dialog.

To manage properties in the Project Properties dialog:

  1. Choose Application > Project Properties (to change or specify a property for just the current project), or Default Project Properties (to set default properties).
  2. In the dialog, choose the page you want. For more information at any time, press F1 or click Help from within the dialog.

As well as managing these preferences and properties, you can also filter schemas or objects in a database connection to just see the ones you want.