24 Getting Started with Working with Databases

This chapter describes how to get started using JDeveloper to work with databases. If you are new to using database you will find the section on connecting to Oracle Database XE, an entry-level, small-footprint database.

This chapter includes the following sections:

24.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:

24.1.1 Connecting to and Working with Databases

Usually you start working with a database by creating a connection to it. 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 Database Navigator, 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.

24.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, and you can edit database objects using the database object editors. Alternatively, you can create an offline database and working either in the Application Navigator or the database diagrammer 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 Application Navigator or Database Navigator, where they are available to applications you are working on, or in the Resource Palette, 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 diagrammer or work with in the Application Navigator.

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 10g Express Edition (Oracle Database XE).

24.2 Getting Started With Oracle Database 10g 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 10g Express Edition (Oracle Database XE). Oracle Database XE is an entry-level, small-footprint database based on the Oracle Database 10g 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 10g 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, follow the instructions in the Getting Started Guide to 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.

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

Table 24-1 Connection details for Oracle Database 10g Express Edition

Field Value

Create Connection In

Choose Application resources. The connection will be displayed in the Application Navigator, 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.

24.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:

  • Database page, where you can choose not to have date and time default values validated, set the default path for export DDL files, and enter the location of a database startup script.

  • Database: Advanced page, where you set options such as the SQL array fetch size and display options for null values.

  • Database: Autotrace/Explain Plan page, where you specify the information to be displayed on the Autotrace and Explain Plan pages in the SQL Worksheet.

  • Database: Drag and Drop page, where you specify the type of SQL statement created in the SQL Worksheet when you drag an object from the Database Navigator into the SQL Worksheet.

  • Database: JDBC Driver Options page, where you register and manage JDBC drivers for the BI JDBC driver, and the WebLogic JDBC drivers for DB2, Informix, SQL Server and Sybase.

  • Database: NLS page, where you specify globalization support parameters, such as the language, territory, sort preference, and date format.

  • Database: Objectives options page, where you specify whether to freeze object viewer windows, and display options for the output.

  • Database: PL/SQL Compiler page, where you specify options for compilation of PL/SQL subprograms.

  • Database: Reports page, where you can choose that database reports in JDeveloper are closed when the database is disconnected.

  • Database: SQL*Plus, where you set the path to the SQL*Plus command line tool.

  • Database: SQL Editor Code Templates page, which allows you to 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 page, which allows you to control how statements in the SQL Worksheet are formatted.

  • Database: User Defined Extensions page. (Not used by JDeveloper.)

  • Database: Worksheet page, where you specify options for the SQL Worksheet.

  • Diagrams: Database (under the Diagrams node). Use to 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.