26 Connecting to and Working with Databases

This chapter describes how to create and work with database connections.

This chapter includes the following sections:

26.1 About Connecting to and with Working with Databases

You can connect to and work with Oracle databases and a number of non-Oracle databases.

Database connections can be available 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 for database objects

  • Search for specific database objects

  • Import and export data

  • Copy a database objects from one database schema to another

  • Compare one database schema to another

  • Export some or all objects of one or more database types to a DLL file

  • Use pre-defined reports and create new reports to provide information about a database and its objects

If you are new to using databases with JDeveloper, one of the easiest ways to get started is to try out Oracle Database Express Edition (Oracle Database XE). For more information, see Section 24.2, "Getting Started With Oracle Database 10g Express Edition."

26.2 Configuring Database Connections

You can define and manage connections to external data sources using the Create Database Connection dialog.

  • The Resource Palette, where they can be added to catalogs to facilitate collaborative working or to make them available to more than one application.

  • The Database Navigator, where you can create, edit, and modify objects in the database.

  • Application Resources in the Application Navigator, where they are available in the current application.

When you delete a connection, JDeveloper does not warn you that a project may be dependent upon it. For this reason, it is best to use caution when deleting connections.

26.2.1 Connection Scope

In JDeveloper 11g you have two ways of creating and managing database connections. You can define database connections for an application (called an Application Resource connection) or for the IDE as a whole (called an IDE connection). You use the same dialog to define these, but their scope within JDeveloper is different.

When you first create a database connection, you choose the connection scope, which you cannot subsequently change. For more information, see Section 3.7.2.2, "Defining the Scope of a Connection."

26.2.2 What Happens When You Create a Database Connection

When you create a database connection, JDeveloper creates a node for the connection in the Database Navigator, and an additional node in either the Resource Palette or in the Application Resources panel of the Application Navigator depending on the scope of the connection.

In the Application Navigator and Database Navigator, you can expand the database connection node to view and work with database objects. In the Resource Palette, you can only work with a database connection after you have added it to the application.

Database Connections Created as Application Resources

Database connections created as application resources are only available to the application in which they are created.

In the Database Navigator, the node for the connection is under the node with the same name as the application.

In the Application Navigator, the node for the connection is under Connections in the Applications Resources panel. Connection information is stored in connections.xml, which is under the Descriptors node, under ADF META-INF. You can open the file in the XML editor by double-clicking it, and you can discover the file path by hovering the mouse over the filename.

The file system location for the connection descriptor definition information is application_folder/.adf/META-INF/connections.xml where application_folder is the path for the selected application.

Database Connections Created as IDE Connections

These database connections are globally defined connections.

You can copy an IDE connection to the application navigator to use it in an application by:

  • From the Resource Palette, dragging the connection and dropping it on the Connections node in the Application Navigator under Application Resources.

  • From the Resource Palette, right-clicking the connection and choosing Add to Application.

  • In the Database Navigator, dragging the connection from under the IDE Connections node to the Application Connections node under the node for the application.

The file system location for the connection descriptor definition information is sys-dir/jdeveloper/system11.1.x.x.nn.nn.nn/o.jdeveloper.rescat2.model/connections/connections.xml.

26.2.3 About Connection Properties Deployment

A connections.xml file is included with JDeveloper deployments, and in the application it is in the folder .adf\META-INF. This file contains the connection information necessary for deployment and the runtime connection execution.

26.2.4 How to Create Database Connections

After you have defined a connection, you can return to the dialog and edit its attributes.

Note:

You cannot change the connection type after the database connection has been created.

To create a database connection:

  1. If necessary, choose View > Database > Database Navigator. Right-click IDE Connections or application, and choose New Connection to open the Create Database Connection dialog.

    Alternatively, from the main menu, choose File > New to open the New Gallery. In the Categories list, expand General and select Connections. In the Items list, double-click Database Connection to open the Create Database Connection dialog.

    For more information at any time, press F1 or click Help from within the Create Database Connection dialog.

  2. Enter the appropriate connection information, then click Test Connection. You may have to briefly wait while JDeveloper connects to the database.

    If the test succeeds, a success message appears in the status text area. If the test does not succeed, an error appears. In this case, change any previously entered information as needed to correct the error, or check the error content to determine other possible sources of the error.

26.2.5 Connecting to Oracle Database Using OCI8

The recommended way of connecting to Oracle Database is using the thin driver, however you can connect using OCI8 (thick connection).

To connect using OCI8:

  • Define the jar location using the system property oracle.jdbc.library. For example:

    jdev -J-Doracle.jdbc.library=/jdev_install/jdeveloper/ojdbc6.jar

26.2.6 How to Edit Database Connections

To edit a database connection:

  1. Choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and select a database connection.

  3. Right-click the connection and choose Properties to open the Edit Database Connection. For more information at any time, press F1 or click Help from within the Create Database Connection dialog.

Note:

You can filter which schemas appear in the connection.

26.2.7 How to Export and import Database Connections

You can also import and export database connections created as IDE connections in the Resource Palette. For more information, see Section 3.7.6, "How to Import and Export Catalogs and Connections."

26.2.7.1 Exporting Database Connections

When you export connections, selected connection descriptors are copied to an XML file. The file can be imported by other users to easily create connections.

To export a database connection:

  1. Choose View > Database > Database Navigator.

  2. Right-click either IDE Connections or application and choose Export Connections.

  3. In the Export Connection Descriptors dialog, enter the filename or click Browse to specify a location and name for the connection file. For more information at any time, press F1 or click Help from within the Export Connection Descriptors dialog.

  4. After you have specified a filename, select the appropriate connections from the list.

    The connection information for the selected connections is saved in the file and can be imported for use by others.

  5. Click OK.

An alternative way of exporting connections, including database connections that are IDE Connections, is to use the Resource Palette.

26.2.7.2 Importing Database Connections

You can import connection descriptors that have previously been exported.

To import a database connection:

  1. Choose View > Database > Database Navigator.

  2. Right-click either IDE Connections or application and choose Import Connections.

  3. In the Import Connection Descriptors dialog, enter the file name of your exported connection file or click Browse to locate it. For more information at any time, press F1 or click Help.

  4. After you have specified a file name, select one or more connections from the list that appears.

  5. Click OK.

An alternative way of importing connections that can include database connections is to use the Resource Palette.

26.2.8 How to Open and Close Database Connections

You can manually connect to a database connection already defined in JDeveloper, or disconnect a database connection.

To open a database connection:

  1. Choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and select a database connection.

  3. Expand the node.

    Alternatively, right-click the closed connection and choose Connect.

To close a database connection:

  1. Choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and select a database connection.

  3. Right-click the connection and choose Disconnect.

26.2.9 How to Delete Database Connections

Deleting connections removes them from the Database Navigator and the installation of JDeveloper.

When you delete a connection, JDeveloper does not warn you that a project may be dependent upon it, and removes the connection from all of JDeveloper, not just a workspace or project. It is best to use caution when deleting connections.

To delete a database connection:

  1. Choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and select a database connection to delete.

  3. Right-click the connection and choose Delete.

  4. In the confirmation dialog, click Yes.

26.2.10 How to Register a New Third-Party JDBC Driver

If you plan to use a third-party JDBC driver for the BI JDBC driver, or the WebLogic JDBC drivers for DB2, Informix, SQL Server and Sybase, you must register it with JDeveloper so that it will be available when you define the connection.

To register a new third-party JDBC driver:

  1. Choose Tools > Preferences.

  2. In the Preferences dialog, select JDBC Driver Options.

  3. The list of third-party JDBC drivers currently registered with JDeveloper is displayed. To add a new entry to the list, click New.

    A new entry appears in the list and in the Driver Class field, with a default driver class name.

  4. In the Driver Class field, alter the new entry to reflect its fully qualified class name.

    Make sure that the correct entry is still selected in the Registered JDBC Drivers list.

  5. Select a library to associate the driver with. You can browse to an existing library, or enter the fully qualified path to the library. The classpath for the library is displayed in Classpath.

    Be sure to include this library in any project that uses the third-party driver.

  6. Click OK.

    The driver will now appear in the list of available third-party JDBC drivers both in this dialog (after you return to it) and in the Create Database Connection dialog.

Alternately, if you are already in the Create Database Connection dialog, you can register a third-party JDBC driver without leaving the dialog. Choose Generic JDBC as the Connection Type, and click New to open the Register JDBC Driver dialog where you provide the class name and library for the driver.

26.2.11 How to Create User Libraries for Non-Oracle Databases

To connect to a non-Oracle database, you first have to create a library containing the JDBC drivers.

After you have created a user library, you can create a database connection.

To create a user library:

  1. Choose Tools > Manage Libraries.

  2. In the Manage Libraries dialog, select the Libraries tab, then select the User node, and click New.

  3. In the Create Library dialog, enter a library name, select the Class Path node, and click Add Entry. In the Select Path Entry dialog, browse to the location of the drivers for the database you are connecting to. Select the driver files, and click Select.

  4. In a similar way, in the Create Library dialog, enter a library name, select the Source Path node, and click Add Entry. In the Select Path Entry dialog, browse to the location of the drivers for the database you are connecting to. Select the driver files, and click Select.

  5. In the Create Library dialog, click OK, and in the Manage Libraries dialog, click OK.

The library containing the JDBC drivers will be available for you to select when you create a connection to the non-Oracle database.

26.2.12 Reference: Connection Requirements for Oracle's Type 2 JDBC Drivers (OCI)

When you create connections using Oracle's JDBC/OCI drivers, be aware of the following platform-specific requirements:

  • You must have the required native libraries (.dll files on Windows, and .so/.sl files on UNIX).

    With the Oracle Type 2 driver (JDBC/OCI), the version of the JDBC driver must match the version of the Oracle home. For example, the Oracle JDBC Driver version 11 requires that Oracle home contain version 11 of ocijdbc11.dll, as well as the Oracle Network software and Required Support Files.

    You can download drivers from the JDBC Driver Downloads page at http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

    If you are connecting to a local database which is a different version from the JDBC driver you are using, then you must install the Oracle client software into a separate Oracle home, and connect via the Oracle Net Listener.

  • You must place the ORACLE_HOME directory in which the client-side file for the required native libraries resides into a directory listed in your PATH environment variable.

    • On Windows: In your PATH environment variable list the %ORACLE_HOME%\bin directory in which the client-side DLL file resides. If you have multiple Oracle homes installed on your machine, use the Oracle home Switch utility to choose the correct Oracle home.

    • On UNIX: List the {ORACLE_HOME}/lib directory in which the client-side .so/.sl file resides in your PATH environment variable.

  • If your Oracle home for the OCI driver is not the same as the Oracle home in which JDeveloper is installed, you must set the ORACLE_HOME environment variable.

  • If your Oracle home for the OCI driver is not the same as the Oracle home in which JDeveloper is installed and you have no other OCI drivers listed in java.library.path, you can edit {$ORACLE_HOME}/jdeveloper/jdev/bin/jdev.conf with a line similar to the following, replacing the path shown with the full path to your Oracle home:

    On Windows: AddNativeCodePath C:/ORACLE/ORAnn/BIN

    On UNIX: AddNativeCodePath /u01/app/oracle/product/n.n.n/lib

    AddNativeCodePath adds to java.library.path the directory name in which the Java VM searches for shared libraries.

    Note:

    Because AddNativeCodePath only appends the directory to the path, if you have an OCI driver path already in the PATH environment variable, set ORACLE_HOME instead of editing PATH with AddNativeCodePath.

26.3 Browsing and Searching Databases

You can control how much of the data source you view and how you view it, and search for database objects.

26.3.1 Browsing Databases

You can browse online databases and offline database objects.

26.3.1.1 Browsing Online Databases

You can browse online databases by opening JDBC connections accessible in the Database Navigator.

JDBC connections permit access to PL/SQL objects and blocks and the Java classes that implement those objects. Any database can be browsed; however only Oracle Database permits access to the full range of database objects.

Database connections are shown in the Database Navigator, under the IDE Connections node or the node for the application. Expand the connection to show the database's schemas. By default, the connection only allows the schema of the user identified in the connection to be browsed. Other schemas can be browsed as well, if the user has the required privileges. Expanding a schema shows nodes for the object types that the schema contains. Expanding the node for an object type show the individual objects it contains. When you have expanded a node as far as it can be expanded, you can double-click an object (or right-click and choose Open) to display its content. Depending on the type of the object, its structure may also be displayed in the structure pane.

26.3.1.2 Browsing Offline Database Objects

You can browse offline database objects using the Application Navigator.

26.3.1.3 How to View Online and Offline Database Objects

You can view database objects:

  • To view database objects through a real time connection (online database), use the Database Navigator.

  • To view offline database objects, use the Application Navigator.

    Changes to database objects in projects (i.e. visible via Application Navigator) can be reconciled against a live database, but until reconciliation, no changes to the offline objects affect online databases.

To open a navigator:

  1. Choose View from the main toolbar.

  2. To open:

    • The Application Navigator, choose Application Navigator.

    • The Database Navigator, choose Database > Database Navigator.

26.3.2 How to Browse online Database Objects

You can browse schemas and the objects they contain via a JDBC connection to an online database.

To browse live database connections:

  1. Choose View > Database > Database Navigator.

  2. Expand a connection to view the schemas available.

  3. Expand a schema to view all the object types visible.

  4. If necessary, apply a filter at the connection, schema, or database object type level.

Note:

By default, a filter is set on tables to exclude those in the recycle bin for an Oracle database.

26.3.3 How to Browse Offline Databases and Schemas

Browse offline databases and schemas in the Application Navigator to find objects such as offline tables or views.

To view offline schemas and the objects they contain:

  1. In the Application Navigator, expand the project containing your offline schemas.

  2. Expand Offline Database Sources and then expand the database and schema you want to browse.

26.3.4 How to Use Database Filters

You can filter schemas, database object types, and database objects within a type, so that a subset that you define is displayed under the connection node. This is useful in environments where there may be thousands of schemas accessible from a connection.

Note:

When you create a connection to Oracle Database, objects for the schema named in the connection are shown. To see the contents of other schemas, expand the Other Users node and then expand the node for the schema you want.

If you connect via Generic JDBC or JDBC-ODBC connections, all schemas are shown.

You can define a filter for schemas in a connection, or for any set of object types (Tables, Views, etc.) within a schema, or for any set of objects within an object type node (for example, display only the tables that begin with DB).

Figure 26-1 Filtered Objects in Database Navigator

Nodes with filters applied in Database Navigator

To use filters:

  1. Choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and select a database connection.

  3. Expand the connection if it has not yet been loaded. Filtering is not available until the connection has been loaded (once per connection per JDeveloper session). Select a connection, schema within a connection, or node within a schema:

  4. If a filter is currently applied to the selection, a filter icon appears on the node of the selected object, and (filtered) appears next to the node name, as shown in Figure 26-1, "Filtered Objects in Database Navigator".

    With the object still selected, click the Apply Filter button in the Database Navigator toolbar and a dialog appears, showing the current selection, if any. From this dialog, you can change the filter currently applied.

26.3.5 How to Enable and Disable Database Filters

JDeveloper provides filters so that you can view defined sets of schemas, tables, views, or other objects.

To create filters for online database objects:

  1. Choose View > Database > Database Navigator.

  2. Expand IDE Connections or application.

  3. Select the connection, schema within a connection, or node within a schema, then perform either of these actions:

    • Right-click your selection and choose Apply Filter.

    • In the Database Navigator toolbar, click the Apply Filter button in the Database Navigator toolbar.

  4. A filter dialog appears, appropriate to the object you selected. For connections and schema, a selection box appears. For other objects, type in the text (case-sensitive) which JDeveloper matches to object names in the selected node. You can use the wildcard character %.

  5. Click OK. Notice that the list of objects is now filtered to display only those names that match the criteria you selected.

26.3.6 How to Open a Database Table in the Database Object Viewer

You can open a table in a live database connection in the Database Object Viewer.

There are a number of tabs along the bottom of the Database Object Viewer that allow you to examine and change the structure of the table and the data contained in the table.

To view and edit the structure of the table in the object viewer:

  1. Open the table in the Database Object Viewer by selecting it in the Database Navigator and double-clicking it. Alternatively, you can right-click the table and choose Open.

  2. Select the tab that contains the information you are interested in, for example, Columns. For more information at any time, press F1 or click Help from within the Database Object Viewer.

An alternative way of viewing and editing the structure of a table is in the Edit Table dialog.

You can edit the data in a table.

26.3.7 How to Edit Table Data

You can change the data in a database table, for example to test the functionality of an application you are developing. You can change the value in a single cell, and add and delete rows. When you have finished you can choose to commit your changes to the database, or to rollback the changes and leave the database table unchanged.

To edit data in a table:

  1. Display the table in the Database Object Viewer by double-clicking it in the Database Navigator.

  2. Click the Data tab to display the contents of the table.

  3. Position the cursor in the cell you want to change and type the new value.

    • To add a new record, click the Insert Row button.

    • To delete one or more records, select them and click the Delete Selected Row(s) button.

  4. When you have finished, either:

    • Click the Commit Changes button to commit your changes to the database.

    • Click the Rollback Changes button to rollback your changes.

26.3.8 How to Find Objects in the Database

You can search for database objects in Oracle Database which has a connection to JDeveloper using the Find Database Object Window.

You must already have a connection to the database.

To find database objects:

  1. From the main menu, choose View > Database > Find DB Object to open the Find Database Object Window.

    For more information at any time, press F1 or click Help from within the navigator.

  2. Select the connection name from the Connection list.

  3. Enter search terms in the Name field. You can use the wildcard % to return a number of matching objects. For example, enter EM% to return all objects with names starting with EM.

  4. If necessary, click More to enter more search criteria.

  5. Click Lookup. The results are returned in the Search window. To view or edit one of the objects (or the parent object that contains the specified object), double-click or right-click its name in the results display.

26.4 Connecting to Databases

This section describes how to connect to Oracle and non-Oracle databases.

26.4.1 What Happens When You Create a Connection to a Database

When you create a database connection using the Create Database Connection dialog, the new connection is created and a node representing the connection is displayed in the:

  • Database Navigator.

  • Application Navigator.

  • Resource Palette.

26.4.2 How to Create Connections to Oracle Databases

You can connect to and work with Oracle databases. For information about the specific versions that are supported, see "JDeveloper Certification Information" at http://www.oracle.com/technetwork/developer-tools/jdev/documentation/index.html.

26.4.2.1 How to Create a Connection to Oracle Database

JDeveloper allows you to connect to:

  • Oracle Database 11g Release 2

  • Oracle Database 11g Release 1

  • Oracle Database 11g Release 2 XE.

  • Oracle Database 10g XE.

  • Oracle Database 10g Release 2

  • Oracle Database 10g Release 1

  • Oracle Database 9i Release 2

You can also connect to (although the connection does not use Oracle (JDBC):

  • MySQL 4.1 or 5.0

  • Oracle TimesTen In-Memory Database

  • Oracle Database Lite 10g Release 1 and Release 3

To create a database connection to Oracle Database:

  1. Use a connection type of Oracle (JDBC).

  2. Enter appropriate username, role, and password values for the database connection.

  3. By default the Save Password field is checked so that you will not be prompted to enter it again.

  4. Select the thin driver.

  5. If the database is on the local machine, use the default of localhost. Otherwise enter an IP address or a host name that can be resolved by TCP/IP, for example, myserver.

  6. Enter either the SID or service name for the database.

  7. Test the connection by clicking Test Connection. You may have to briefly wait while JDeveloper connects to the database.

If the test succeeds, a success message appears in the status text area. If the test does not succeed, an error appears. In this case, change any previously entered information as needed to correct the error, or check the error content to determine other possible sources of the error.

26.4.2.2 How to Create a Connection to MySQL

JDeveloper allows you to connect to MySQL 4.1 or, 5.0, or to emulate MySQL 4.1 or, 5.0 for offline database operations. For more information about MySQL, see http://www.oracle.com/us/products/mysql/index.htm.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with MySQL. You can:

  • Create tables:

    • Add column(s) specifying data types, NOT NULL constraints, default values and column comments

    • Add primary key and foreign key constraints

  • Alter tables:

    • Add column(s)

    • Drop column(s)

    • Add index

    • Drop index

    • Add constraint (primary key, unique key, and foreign key)

    • Drop constraint (primary key, unique key, and foreign key)

  • Rename table

  • Drop table

To create a database connection to MySQL:

  1. From http://mysql.com/downloads, download and install MySQL Connector/J 3.1.

  2. Set up the user library to contain the following mysql-connector-java-3.1.8-bin.jar.

  3. Create a database connection to MySQL. Use the following values:

    • Connection Type: MySQL

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: com.mysql.jdbc.Driver

    • Library: the library you created for the driver.

    • JDBC URL: jdbc:mysql://machine-name/database-name

26.4.2.3 How to Create a Connection to Oracle TimesTen In-Memory Database

Oracle TimesTen In-Memory Database is a memory-optimized relational database that provides applications with extremely fast response time and very high throughput as required by many application in a wide range of industries. Deployed in the application tier, TimesTen databases reside entirely in physical memory with persistence to disk storage for recoverability.

JDeveloper allows you to connect to Oracle TimesTen In-Memory Database 6.0, 7.0, or 11g, or to emulate TimesTen databases for offline database operations. For more information about Oracle TimesTen In-Memory Database 11g, see http://www.oracle.com/technetwork/database/timesten/overview/index.html.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with TimesTen databases. You can:

  • Create tables.

    • Add columns

    • Add primary keys and foreign keys

  • Alter tables.

    • Add columns

    • Drop columns

    • Add primary keys and foreign keys

    • Drop primary keys and foreign keys

  • For Oracle TimesTen In-Memory Database v6.0, a current limitation is that in order to see constraints such as primary keys, you must ensure that your connection username is the same as the name of the schema you are connecting to.

To create a database connection to Oracle TimesTen In-Memory Database:

  1. Create a database connection to the TimesTen database.

  2. Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: leave blank

    • Driver Class: com.timesten.jdbc.TimesTenDriver

    • Library:

      • Release 6.0.1: timesten-install\tt60\lib\classes14.jar

      • Release 7.0.5: timesten-install\tt70_32\lib\ttjdbc5.jar

      • Release 11.2.1: timesten-install\tt1121_32\lib\ttjdbc5.jar

    • JDBC URL:

      • Release 6.0.1: jdbc:timesten:client:RunDataCS60

      • Release 7.0.5: jdbc:timesten:client:RunDataCS_tt70_32

      • Release 11.2.1: jdbc:timesten:client:cachealone1_CS

26.4.2.4 How to Create a Connection to Oracle Database Lite

Oracle Database Lite allows an image of an Oracle database to exist on a remote device. Users can update the data on Oracle Database Lite and commit it to the main database at given intervals. For more information about Oracle Database Lite 10g, see http://www.oracle.com/technetwork/database/database-lite/overview/index.html.

JDeveloper supports connections to and database emulation of Oracle Database Lite 10g Release 1 and Release 3. For information, see "JDeveloper Certification Information" at http://www.oracle.com/technetwork/developer-tools/jdev/documentation/index.html.

This driver requires installation of Oracle Database Lite 4.0 JAR or higher. Projects using the driver must include ORACLE_HOME/lite/classes/olite.jar in a library.

To create a database connection to Oracle Database Lite:

  1. Download a Java JDBC driver for Oracle Database Lite. Download and install Oracle Database Lite 4.0 JAR or higher, and create a library to include oracle-database-lite/lite/classes/olite.jar.

  2. If you are using a type 2 driver, you must edit the ide.conf file to provide one new value:

    1. Close JDeveloper.

    2. In a text editor, open ide.conf in the jdev-install/ide/bin directory.

    3. Add the new entry

      AddJavaLibPath oracle-database-lite\olite40.jar

  3. Create a database connection to the Oracle Database Lite.

    Use the following values:

    • Connection Type: Oracle Lite

    • Username and Password: enter the appropriate values for the connection.

    • Driver: Type 2

    • Datasource name: enter an appropriate value for the connection.

26.4.3 How to Create Connections to Non-Oracle Databases

You can connect to and work with non-Oracle databases. For information about the specific versions that are supported, see "JDeveloper Certification Information" at http://www.oracle.com/technetwork/developer-tools/jdev/documentation/index.html.

In general, you can:

  • Import database objects to JDeveloper.

  • Create offline database objects.

  • Edit offline database objects.

Creating a database connection:

  1. Create a library containing the JDBC drivers.

  2. Create a database connection.

  3. In the Create Database Connection dialog, enter the appropriate values for the database. For more information, refer to the help topic for the database you are connecting to.

  4. Finally, you must configure your projects to use the correct data types.

In the descriptions below for specific types of connection the JDBC URL is shown, however if you prefer you can enter details of the server, port, and database in the fields of the Create Database Connection dialog.

26.4.3.1 How to Create a Connection to Apache Derby

Apache Derby is an open source relational database implemented entirely in Java. JDeveloper allows you to connect to Apache Derby 10.5, or to emulate Apache Derby 10.5 for offline database operations. For more information about Apache Derby, see http://db.apache.org.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with Apache Derby. You can:

  • Create tables:

    • Add column(s)

    • Add primary key and foreign key constraints

  • Alter tables:

    • Add column(s)

    • Drop column(s)

    • Add constraint

    • Drop constraint

Note:

Column default values are not supported

You can connect to Apache Derby using Derby's embedded JDBC driver or you can create a connection as a client.

To connect to Apache Derby using the embedded driver:

  1. Create a database connection to the Apache Derby database.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: org.apache.derby.jdbc.EmbeddedDriver

    • Library: lib/derbyclient.jar

    • JDBC URL: jdbc:derby://machine-name:port/databases/database-name

To connect to Apache Derby as a client:

  1. Create a database connection to the Apache Derby database.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: org.apache.derby.jdbc.ClientDriver

    • Library: lib/derbyclient.jar

    • JDBC URL: jdbc:derby://machine-name:port/databases/database-name

26.4.3.2 How to Create a Connection to IBM DB2 Universal Database

JDeveloper allows you to connect to IBM DB2 Universal Database 9.5 or 8.1, or to emulate IBM DB2 Universal Database 9.5 or 8.1 for offline database operations. For more information about IBM DB2 Universal Database, see http://www.ibm.com.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with IBM DB2, and working with IBM DB2 databases is subject to the following limitations:

  • Create tables, and add columns specifying Datatypes, NOT NULL constraints and default values, add primary and foreign keys, and create indexes.

  • Alter tables, and add and drop columns, add and drop indexes, add and drop constraints (primary keys, unique keys, check and foreign keys).

  • Rename tables.

  • Drop tables.

Notes:

IBM DB2 Universal Database 9.5 syntax of DROP column and ALTER COLUMN is supported for IBM DB2 Universal Database 9.5.

You can only connect to DB2 Universal Database 8.1 with Fix Patch 3 or higher, or to DB2 Universal Database 9.5. When you have a DB2 connection, column and constraint information is not displayed in the Database Navigator. Instead columns and constraints are displayed in the Structure window when the table is selected in the Database Navigator.

You can connect to IBM DB2 using the WebLogic JDBC driver or using IBM's native driver.

To connect to IBM DB2 using the WebLogic JDBC driver:

  1. Create a database connection to the IBM DB2 database.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: weblogic.jdbc.db2.DB2Driver

    • JDBC URL: jdbc:weblogic:db2://machine-name:port;DatabaseName=database-name

To connect to IBM DB2 using the native driver:

  1. Download the Type 4 JDBC driver for IBM DB2.

  2. Set up the user library to contain the following files.

    • DB2 UDB 8.1

      • db2jcc.jar

      • db2jcc_javax.jar

      • db2jcc_license_cu.jar

    • DB2 UDB 9.5

      • db2jcc.jar

      • db2jcc4.jar

  3. Create a database connection to IBM DB2.

    Use the following values:

    • Connection Type: DB2 UDB

    • Username and Password: enter appropriate values for the database connection.

    • Driver Class: com.ibm.db2.jcc.DB2Driver

    • Library: the library you created for the driver.

    • JDBC URL: jdbc:db2://machine-name:50000/database-name

26.4.3.3 How to Create a Connection to IBM Informix Dynamic Server

JDeveloper allows you to connect to IBM Informix DS 10 or 11.5, or to emulate IBM Informix DS 10 or 11.5 for offline database operations. For more information about IBM Informix DS, see www.IBM.com.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with IBM Informix DS. You can:

  • Create tables, and add columns.

  • Add primary key and foreign key constraints.

  • Alter tables, add columns, and drop columns.

You can connect to IBM Informix DS using the WebLogic JDBC driver or using IBM's native driver.

To connect to IBM Informix DS using the WebLogic JDBC driver:

  1. Create a database connection to the IBM Informix DS database.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: weblogic.jdbc.informix.InformixDriver

    • JDBC URL: jdbc:weblogic:informix://machine-name:port;informixServer=server-name;databaseName=database-name

To connect to IBM Informix DS using native drivers:

  1. From www.IBM.com, download and install the appropriate Informix JDBC Driver:

    • For IBM Informix DS 10, choose v2.21.JC5 or later.

    • For IBM Informix DS 11.5, choose v3.00.JC3 or later.

  2. Set up the user library to contain install-directory\lib\ifxjdbc.jar.

  3. Create a database connection to IBM Informix DS.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: com.informix.jdbc.IfxDriver

    • Library: the library you created for the driver.

    • JDBC URL: jdbc:informix-sqli://machine-name:port/database-name:INFORMIXSERVER=machine-name

26.4.3.4 How to Create a Connection to Microsoft SQL Server

JDeveloper allows you to connect to Microsoft SQL Server 2005, or 2008, or to emulate Microsoft SQL Server 2005, or 2008 for offline database operations. For more information about Microsoft SQL Server, see http://www.microsoft.com.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with Microsoft SQL Server. You can:

  • Create tables:

    • Add column(s) specifying data types, NOT NULL constraints, default values and column comments

    • Add primary key and foreign key constraints

    • Create indexes

  • Alter tables:

    • Add column(s)

    • Drop column(s)

    • Add indexes

    • Drop indexes

    • Add constraint (primary key, unique key, and foreign key)

    • Drop constraint (primary key, unique key, and foreign key)

  • Drop tables

You can connect to Microsoft SQL Server using the WebLogic JDBC driver or using Microsoft's native driver.

To connect to Microsoft SQL Server using the WebLogic JDBC driver:

  1. Create a database connection to the Microsoft SQL Server database.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: weblogic.jdbc.sqlserver.SQLServerDriver

    • JDBC URL: jdbc:weblogic:sqlserver://machine-name\MSSQLSERVER:port;databaseName=database-name

To connect to Microsoft SQL Server:

  1. From www.microsoft.com, download and install the appropriate Microsoft SQL Server driver:

    • For Microsoft SQL Server 2005, choose Microsoft SQL Server 2005 Driver.

    • For Microsoft SQL Server 2008, choose Microsoft SQL Server 2008 Driver.

  2. Set up the user library to contain install-directory\sqljdbc.jar.

  3. Create a database connection to Microsoft SQL Server. Use the following values:

    • Connection Type: SQLServer

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

    • Library: the library you created for the driver.

    • JDBC URLs: jdbc:sqlserver://machine-name:port;DatabaseName=database-name, where the section DatabaseName=database-name is optional

What you May Need to Know

If you are using Windows Authentication credentials to connect to Microsoft SQL Server, you need to add do the following:

  • Add the connection property integratedSecurity=TRUE and the username and password values to the JDBC URL, for example

    jdbc:sqlserver://machine-name:port;DatabaseName=database-name;username=USERNAME;password=PASSWORD;integratedSecurity=TRUE

  • Add the location of sqljdbc_auth.dll to your PATH variable:

    • For 32bit JVM, this is installation-directory\sqljdbc_version\language\auth\x86

    • For 64bit JVM, this is installation-directory\sqljdbc_version\language\auth\x64

For more information, see Building the Connection URL, which is available as part of Connecting to SQL Server with the JDBC Driver at the Microsoft MSDN website.

26.4.3.5 How to Create a Connection to SQLite

SQLite is a relational database management system represented by a platform-independent file that resides on a host computer, for example, smartphone platforms. JDeveloper allows you to connect to a SQLite 3.6 database file, or to emulate SQLite 3.6 for offline database operations. For more information about SQLite, see http://www.sqlite.org.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with SQLite. You can:

  • Create tables, and add columns.

  • Alter tables, and add columns.

  • Copy To Project, where you copy tables and their columns and primary keys from a connection to a SQLite database to an offline database which emulates SQLite.

  • Constraints, indexes and the column properties can be modeled in an offline database, but DDL is only generated for tables and columns; there is no support for generating constraints (including primary keys) on tables, or generating any other object type (for example, indexes, views, triggers). This means that for tables in an online SQLite database, the Create/Edit Table dialog only shows the columns panel.

To create a database connection to SQLite:

  • Download a Java JDBC driver for SQLite and create a library for it.

  • Create a database connection to SQLite.

  • Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: leave blank

    • Driver Class: org.sqlite.JDBC

    • Library: the library you created for the driver.

    • JDBC URL: jdbc:sqlite://path/database-name, where path is the path of the database file and database-name is the name of the SQLite database at the specified location. If the database does not exist at specified location, it will be created when the connection is made.

26.4.3.6 How to Create a Connection to Sybase ASE

JDeveloper allows you to connect to Sybase Adaptive Server Enterprise 12.5 or 15, or to emulate Sybase ASE 12.5 or 15 for offline database operations. For more information about Sybase Adaptive Server Enterprise, see www.sybase.com.

The Create Table or Edit Table dialog is generic, and some features may not be available when working with Sybase ASE. You can:

  • Create tables:

    • Add column(s)

    • Add primary key and foreign key constraints

    Add column(s)

  • Alter tables:

    • Add column(s)

    • Drop column(s)

    • Add constraint

    • Drop constraint

Note:

Column default values are not supported

You can connect to Sybase ASE using the WebLogic JDBC driver or using Sybase's native driver.

To connect to Sybase ASE using the WebLogic JDBC driver:

  1. Create a database connection to the Sybase ASE database.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class: weblogic.jdbc.sybase.SybaseDriver

    • JDBC URL: jdbc:weblogic:sybase://machine-name:port;DatabaseName=databas-name

To connect to Sybase ASE using the native driver:

  1. From www.sybase.com, download and install the appropriate Sybase JDBC driver:

    • For Sybase ASE 12.5, choose jConnect Version:5.5 or later.

    • For Sybase ASE 15, choose jConnect Version: 6.0.5 or later.

  2. Set up the user library to contain the following:

    install-directory\jConnect-5_5\classes\jconn2.jar install_directory\\jConnect-5_5\classes\jTDS2.jar

  3. Create a database connection to Sybase ASE.

    Use the following values:

    • Connection Type: Generic JDBC

    • Username and Password: enter the appropriate values for the connection.

    • Driver Class:

      • For Sybase ASE 12.5, use com.sybase.jdbc2.jdbc.SybDriver

      • For Sybase ASE 15 use com.sybase.jdbc3.jdbc.SybDriver

    • Library: the library you created for the driver.

    • JDBC URL: jdbc:sybase:Tds:machine:port/database-name

26.5 Importing and Exporting Data

You can import data into tables in a database through a database connection.

Note:

You cannot import date into offline tables as offline tables are just representations of database tables.

You can import data from:

  • csv, a file containing comma-separated values including a header row for column identifiers.

  • xls, a file in Microsoft Excel format (only for import into existing and new tables).

You can import the data into:

  • A existing table in the database.

  • A new table that you create as part of the import process.

  • Using a SQL*Loader control file.

  • An external table.

26.5.1 Importing Data Using SQL*Loader

When you choose the SQL*Loader option in the Data Import Wizard, JDeveloper creates the following files in the same location as the import file containing the data: table.ctl, which contains information about the file containing the data and the table into which it can be imported. table.bat and table.sh, to run the import.

26.5.2 Importing Data Into an External Table

You can import data into an external table, which is a flat file in which you can query data as though it were an Oracle table.

When you choose the External Table option, JDeveloper creates the SQL and displays it in the SQL Worksheet where you can examine it and make any necessary changes before running the script.

26.5.3 How to Import Data into Existing Tables

You can import data into a table in a database through a database connection.

The following import file formats are supported:

  • csv, a file containing comma-separated values including a header row for column identifiers.

  • xls, a file in Microsoft Excel format.

To import data to an existing database table:

  1. From the main menu, choose View > Database > Database Navigator to open the Database Navigator.

  2. If necessary, create a connection to the database.

  3. Expand the node for the database connection, the schema, Tables, and select the table node you want to import data to.

  4. Right-click and choose Import Data and in the Open dialog enter or browse to the location of the file.

    Click OK to launch the Data Import Wizard.

    For more information at any time, press F1 or click Help from within the wizard.

  5. On the Column Definition page of the Data Import wizard, enter the name of the new table.

26.5.4 How to Import Data to New Tables

You can import data into a database table that you create as part of the import process.

To import data to a new database table:

  1. From the main menu, choose View > Database > Database Navigator to open the Database Navigator.

  2. If necessary, create a connection to the database.

  3. Expand the node for the database connection, the schema, Tables, and select the table node you want to import data to.

  4. Right-click and choose Import Data and in the Open dialog enter or browse to the location of the file. Click OK to launch the Data Import Wizard.

    For more information at any time, press F1 or click Help from within the wizard.

  5. On the Column Definition page of the Data Import wizard, enter the name of the new table.

26.5.5 How to Import Data Using SQL*Loader

You can create a SQL*Loader control file which can be used to import data.

To import data to a SQL*Loader control file:

  1. From the main menu, choose View > Database > Database Navigator to open the Database Navigator.

  2. If necessary, create a connection to the database.

  3. Expand the node for the database connection, the schema, Tables, and select the table node you want to import data to.

  4. Right-click and choose Import Data and in the Open dialog enter or browse to the location of the file.

    Click OK to launch the Data Import Wizard.

    For more information at any time, press F1 or click Help from within the wizard.

  5. On the Data Preview page of the Data Import wizard, choose SQL*Loader Table.

  6. On the Options page of the Data Import wizard, choose the options for the generated file.

  7. When you complete the Data Import wizard, the SQL*Loader control file called table.ctl is created in the same location as the data file, along with a table.bat and table.sh files which allow you to run it. If you selected Send to worksheet on the Finish page of the Data Import wizard, the SQL defining the table is displayed in the SQL Worksheet.

26.5.6 How to Import Data Using External Tables

You can import data into tables in a database through a database connection.

To import data to an external table:

  1. If necessary, create a connection to the database.

  2. Expand the node for the database connection, the schema, Tables, and select the table node you want to import data to.

  3. Expand the node for the database connection, the schema, Tables, and select the table node you want to import data to.

  4. Right-click and choose Import Data and in the Open dialog enter or browse to the location of the file.

  5. Click OK to launch the Data Import Wizard.

    For more information at any time, press F1 or click Help from within the wizard.

  6. On the Data Preview page of the Data Import wizard, choose External Table.

  7. On the Options page of the Data Import wizard, choose the options for the generated file. When you complete the Data Import wizard, the SQL is displayed in the SQL Worksheet, where you can examine it and make any changes. When you are satisfied, right-click in the Worksheet, and choose Run in SQL*Plus.

26.5.7 Exporting Data from Databases

You can export data from tables in a database through a database connection.

The data can be saved to a file or to the clipboard. The following formats are supported:

  • csv, to create a file containing comma-separated values including a header row for column identifiers.

    Note:

    You can choose a different delimiter.

  • fixed, to create a file where records are the same byte length.

  • html, to create an HTML file containing a table with the data. insert, to create a file containing SQL INSERT statements.

  • loader, to create a SQL*Loader control file.

  • text, to create a text file.

  • ttbulkcp, to create a data files to be used with the TimesTen ttbulkcp command line utility. For more information, see Oracle TimesTen In-Memory Database 11g at http://www.oracle.com/technology/products/timesten/index.html.

  • xls, to create a Microsoft Excel .xls file. The file will contain two worksheets, Export Worksheet, which contains the data, and SQL which contains the SQL statement used to export the data.

  • xml, to create a file containing XML tags and data.

In the Export Data dialog, you can limit the data to be exported by selecting only some columns, and by entering a WHERE clause.

Note:

If you encounter problems exporting large tables to Microsoft Excel files, try adding the following line to the jdeveloper.conf file to increase heap size, and then restarting JDeveloper: 
AddVMOption -Xmx1024M

If the number of table rows exceeds 65,536, JDeveloper writes the rows to multiple worksheets within the .xls file.

You can also export data from a database using the Export Database wizard.

26.5.8 How to Export Data to Files

You can export data from tables in a database through a database connection.

To export data from a database table:

  1. From the main menu, choose View > Database > Database Navigator to open the Database Navigator.

  2. If necessary, create a connection to the database.

  3. Expand the node for the database connection, the schema, Tables, and select the table node you want to export data from.

  4. Right-click and choose Export Data to open the Export Data dialog.

    For more information at any time, press F1 or click Help from within the wizard.

26.6 Copying, Comparing, and Exporting Databases

You can copy database objects from a source schema to a destination schema. You can export database objects and data to a DDL file.

26.6.1 How to Copy Databases

You can copy database objects from a source schema to a destination schema, subject to any restrictions depending on the type of operation, which determines the behavior if objects of the same name exist in the destination schema.

You must have the source and the destination database connections already defined.

To copy a database:

  1. From the main menu, choose Tools > Database > Database Copy to open the New Copy wizard.

    For more information at any time, press F1 or click Help from within the wizard.

26.6.2 How to Compare Database Schemas

You can find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally update the objects in one schema (destination) to reflect differences in the other schema (source).

Using the Diff wizard requires the licensing of the Oracle Change Management option for Oracle Database. To purchase a license, contact your Oracle sales representative or authorized Oracle Reseller, or go to the Oracle Store to buy online at http://shop.oracle.com

You must have the source and the destination database connections already defined.

To compare database schemas:

  1. From the main menu, choose Tools > Database > Database Diff to open the Diff wizard.

    For more information at any time, press F1 or click Help from within the wizard.

26.6.3 How to Export Databases

You can export some or all objects of one or more types of database objects to a file containing SQL data definition language (DDL) statements to create these objects. Export Database wizard allows you to: Specify details of the DDL file that is generated. Select the database object objects to be exported. Choose to export data, and apply filters to specify the data to be included in the generated file.

You must have already defined a connection to the database you want to export.

To export a database:

  1. From the main menu, choose Tools > Database > Database Export to open the Export Database wizard.

    For more information at any time, press F1 or click Help from within the wizard.

26.7 Working with Oracle and Non-Oracle Databases

This section describes how to work with Oracle Database, as well as with non-Oracle databases. There are limitations on what you can do with JDeveloper with different databases. For more information, see the relevant information in Section 26.4.2, "How to Create Connections to Oracle Databases" and Section 26.4.3, "How to Create Connections to Non-Oracle Databases."

26.8 Working with Database Reports

JDeveloper provides many reports about a database and its objects. You can also create your own user-defined database reports.

You can also run reports on offline database objects.

26.8.1 Using Database Reports

JDeveloper provides many reports about a database and its objects. You can also create your own user-defined database reports.

For some reports, you are prompted for bind variables before the report is generated. These bind variables enable you to further restrict the output. The default value for all bind variables is null, which implies no further restrictions.

The Database Reports Navigator allows you to run reports which query the database for the latest information. The time required to display specific reports will vary, and may be affected by the number and complexity of objects involved, and by the speed of the network connection to the database.

There are a number of predefined reports about the database and its objects.

You can also create your own user-defined reports.

You can examine the underlying SQL for a report, for example, to help you create your own report.

Database reports are organized in folders, and reports and folders can be exported.

You can share reports by exporting them.

The person who wants to share the report then adds it to their instance of JDeveloper using the Preferences dialog. Reports that have been exported can be imported into folders under the User Defined Reports node.

26.8.1.1 How to Run Database Reports

The Database Reports Navigator allows you to run reports which query the database for the latest information. The time required to display specific reports will vary, and may be affected by the number and complexity of objects involved, and by the speed of the network connection to the database.

Running a database report:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Locate the report you want to run, right-click and choose Open, which will overwrite any previous results in the Reports Viewer window, or Open New to open a new instance of the Reports Viewer.

  3. If the Bind Variables dialog is displayed, enter the bind variables you want to use. For more information at any time, click F1 or Help in the Bind Variables dialog.

The report results are displayed in the Reports Viewer.

26.8.1.2 How to View the SQL for a Report

You can view the underlying SQL for a database report in the SQL Worksheet.

To view the SQL for a database report:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Run the report.

  3. In the Reports Viewer, click the Run Report in SQL Worksheet button. The SQL Worksheet opens displaying the SQL code for the report.

26.8.1.3 How to Create User-Defined Database Reports

You can define your own reports for database features and objects.

To create user-defined reports:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Right-click the User Defined Reports node, or a folder that you have created under this node, and choose Add Report.

  3. In the Create Report dialog, enter a name and the SQL for the report. For more information at any time, click F1 or Help in the Create Report dialog.

26.8.1.4 How to Edit User-Defined Database Reports

You can edit user-defined reports.

To edit a user-defined report:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Open the User Defined Reports node, and right-click on the report you want to edit, and choose Edit.

  3. In the Create Report dialog, enter a name and the SQL for the report. For more information at any time, click F1 or Help in the Create Report dialog.

26.8.1.5 How to Create Reports Folders

You can organize user-defined reports in folders.

To create a folder:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Right-click the User Defined Reports node, and choose Add Folder.

  3. In the Create Folder dialog, enter a name for the folder. For more information at any time, click F1 or Help in the dialog

26.8.1.6 How to Export User-Defined Reports

You can export database reports or folders of database reports.

If you are sharing a report, you export it, and users who want to share the report, then make it available in their instance of JDeveloper.

To export a database report or folder:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Right-click the report or folder you want to share, and choose Export.

  3. Enter a location for the report in the Save dialog. The default name for the report is explain.xml.

26.8.1.7 How to Import User-Defined Reports

After you have exported database reports and folders, you can import them to a user-defined folder.

You need to first create the folder to hold the report.

This can also be a simple way to share database reports.

To import a database report or folder:

  1. If it is not open, open the Database Reports Navigator. In the main menu, choose View > Database > Database Reports.

  2. Under the User Defined Reports node, right-click the folder you want to add the report to, and choose Import.

  3. In the Open dialog, enter or browse to the location for the exported report in the Save dialog. The default name for the report is explain.xml.

How to Share Database Reports

You can share database reports. The report is exported, then you add it to your invocation of JDeveloper.

Before a report can be shared:

  • The report must be run.

  • The report must then be exported.

Sharing a database report:

  1. From the main menu, choose Tools > Preferences.

  2. In the Preferences dialog, select Database > User-Defined Extensions. For more information at any time, press F1 or click Help from within the Preferences dialog.

  3. Click Add Row, and under Type select REPORT, and under Location enter or browse to the location of the exported report.

  4. Restart JDeveloper.

  5. Choose View > Database > Database Reports to open the Database Reports Navigator. The shared report is listed under the Shared Reports node in the navigator.

26.8.2 Reference: Pre-Defined Database Reports

This section describes the pre-defined reports available under the Data Dictionary Reports node in the Database Reports navigator.

The reports are grouped into categories, with one or more different reports available in that category.

  • About Your Database Reports

    These reports list release information about the database associated with the connection. The reports include Version Banner (database settings) and National Language Support Parameters (NLS_xxx parameter values for globalization support).

  • All Objects Reports

    These reports list information about all objects accessible to the user associated with the specified database connection, not just objects owned by the user.

    • All Objects: For each object, lists the owner, name, type (table, view, index, and so on), status (valid or invalid), the date it was created, and the date when the last data definition language (DDL) operation was performed on it. The Last DDL date can help you to find if any changes to the object definitions have been made on or after a specific time.

    • Collection Types: Lists information about each collection type. The information includes the type owner, element type name and owner, and type-dependent specific information.

    • Dependencies: For each object with references to it, lists information about references to (uses of) that object.

    • Invalid Objects: Lists all objects that have a status of invalid.

    • Object Count by Type: For each type of object associated with a specific owner, lists the number of objects. This report might help you to identify users that have created an especially large number of objects, particularly objects of a specific type.

    • Public Database Links: Lists all public database links.

    • Public Synonyms: Lists all public synonyms.

  • Application Express Reports

    These reports list information about Oracle Application Express 3.0.1 (or later) applications, pages, schemas, UI defaults, and workspaces. If you select a connection for a schema that owns any Oracle Application Express 3.0.1 (or later) applications, the Application Express reports list information about applications, pages, schemas, UI defaults, and workspaces. For more information, see Oracle Application Express Administration Guide.

  • ASH and AWR Reports

    These reports list information provided by the Active Session History (ASH) and Automated Workload Repository (AWR) features.

  • Database Administration Reports

    These reports list usage information about system resources. This information can help you to manage storage, user accounts, and sessions efficiently. (The user for the database connection must have the DBA role to see most Database Administration reports.)

    • All Tables: Contains the reports that are also grouped under Table reports, including Quality Assurance reports.

    • Cursors: Provide information about cursors, including cursors by session (including open cursors and cursor details.

    • Database Parameters: Provide information about all database parameters or only those parameters that are not set to their default values.

    • Locks: Provide information about locks, including the user associated with each.

    • Sessions: Provide information about sessions, selected and ordered by various criteria.

    • Storage: Provide usage and allocation information for tablespaces and data files.

    • Top SQL: Provide information about SQL statements, selected and ordered by various criteria. This information might help you to identify SQL statements that are being executed more often than expected or that are taking more time than expected.

    • Users: Provide information about database users, selected and ordered by various criteria. For example, you can find out which users were created most recently, which user accounts have expired, and which users use object types and how many objects each owns.

  • Data Dictionary Reports

    These reports list information about the data dictionary views that are accessible in the database. Examples of data dictionary views are ALL_OBJECTS and USER_TABLES.

    • Dictionary View Columns: For each Oracle data dictionary view, lists information about the columns in the view.

    • Dictionary Views: Lists each Oracle data dictionary view and (in most cases) a comment describing its contents or purpose.

  • Jobs Reports

    These reports list information about jobs running on the database.

    • All Jobs: Lists information about all jobs running on the database. The information includes the start time of its last run, current run, and next scheduled run.

    • DBA Jobs: Lists information about each job for which a DBA user is associated with the database connection. The information includes the start time of its last run, current run, and next scheduled run.

    • Your Jobs: Lists information about each job for which the user associated with the database connection is the log user, privilege user, or schema user. The information includes the start time of its last run, current run, and next scheduled run.

  • PLSQL Reports

    These reports list information about your PL/SQL objects and allow you to search the source of those objects.

    • Program Unit Arguments: For each argument (parameter) in a program unit, lists the program unit name, the argument position (1, 2, 3, and so on), the argument name, and whether the argument is input-only (In), output-only (Out), or both input and output (In/Out).

    • Search Source Code: For each PL/SQL object, lists the source code for each line, and allows the source to be searched for occurrences of the specified variable.

    • Unit Line Counts: For each PL/SQL object, lists the number of source code lines. This information can help you to identify complex objects (for example, to identify code that may need to be simplified or divided into several objects).

  • Security Reports

    These reports list information about users that have been granted privileges, and in some cases about the users that granted the privileges. This information can help you (or the database administrator if you are not a DBA) to understand possible security issues and vulnerabilities, and to decide on the appropriate action to take (for example, revoking certain privileges from users that do not need those privileges).

    • Auditing: Lists information about audit policies.

    • Encryption: Lists information about encrypted columns.

    • Grants and Privileges: Includes the following reports:

      • Column Privileges: For each privilege granted on a specific column in a specific table, lists the user that granted the privilege, the user to which the privilege was granted, the table, the privilege, and whether the user to which the privilege was granted can grant that privilege to other users.

      • Object Grants: For each privilege granted on a specific table, lists the user that granted the privilege, the user to which the privilege was granted, the table, the privilege, and whether the user to which the privilege was granted can grant that privilege to other users.

      • Role Privileges: For each granted role, lists the user to which the role was granted, the role, whether the role was granted with the ADMIN option, and whether the role is designated as a default role for the user.

      • System Privileges: For each privilege granted to the user associated with the database connection, lists the privilege and whether it was granted with the ADMIN option.

    • Policies: Lists information about policies.

    • Public Grants: Lists information about privileges granted to the PUBLIC role.

  • Streams Reports

    These reports list information about stream rules.

    • All Stream Rules: Lists information about all stream rules. The information includes stream type and name, rule set owner and name, rule owner and name, rule set type, streams rule type, and subsetting operation.

    • Your Stream Rules: Lists information about each stream rule for which the user associated with the database connection is the rule owner or rule set owner. The information includes stream type and name, rule set owner and name, rule owner and name, rule set type, streams rule type, and subsetting operation.

  • Table Reports

    These reports list information about tables owned by the user associated with the specified connection. This information is not specifically designed to identify problem areas; however, depending on your resources and requirements, some of the information might indicate things that you should monitor or address.

    For table reports, the owner is the user associated with the database connection.

    • Columns: For each table, lists each column, its data type, and whether it can contain a null value. Also includes:

    • Data type Occurrences: For each table owner, lists each data type and how many times it is used.

    • Comments for tables and columns: For each table and for each column in each table, lists the descriptive comments (if any) associated with it. Also includes a report of tables without comments. If database developers use the COMMENT statement when creating or modifying tables, this report can provide useful information about the purposes of tables and columns

    • Constraints: Includes the following reports related to constraints:

    • All Constraints: For each table, lists each associated constraint, including its type (unique constraint, check constraint, primary key, foreign key) and status (enabled or disabled).

    • Check Constraints: For each check constraint, lists information that includes the owner, the table name, the constraint name, the constraint status (enabled or disabled), and the constraint specification.

    • Enabled Constraints and Disabled Constraints: For each constraint with a status of enabled or disabled, lists the table name, constraint name, constraint type (unique constraint, check constraint, primary key, foreign key), and status. A disabled constraint is not enforced when rows are added or modified; to have a disabled constraint enforced, you must edit the table and set the status of the constraint to Enabled (see the appropriate tabs for the Create/Edit Table (with advanced options) dialog box).

    • Foreign Key Constraints: For each foreign key constraint, lists information that includes the owner, the table name, the constraint name, the column that the constraint is against, the table that the constraint references, and the constraint in the table that is referenced.

    • Primary Key Constraints: For primary key constraint, lists information that includes the owner, the table name, the constraint name, the constraint status (enabled or disabled), and the column name.

    • Unique Constraints: For each unique constraint, lists information that includes the owner, the table name, the constraint name, the constraint status (enabled or disabled), and the column name.

    • Indexes: Includes information about all indexes, indexes by status, indexes by type, and unused indexes.

    • Organization: Specialized reports list information about partitioned tables, clustered tables, and index-organized tables.

    • Quality Assurance: (See Quality Assurance reports.)

    • Statistics: For each table, lists statistical information, including when it was last analyzed, the total number of rows, the average row length, and the table type. In addition, specialized reports order the results by most rows and largest average row length.

    • Storage: Lists information about the table count by tablespace and the tables in each tablespace.

    • Triggers: Lists information about all triggers, disabled triggers, and enabled triggers.

    • User Synonyms: Displays information about either all user synonyms or those user synonyms containing the string that you specify in the Enter Bind Variables dialog box (deselect Null in that box to enter a string).

    • User Tables: Displays information about either all tables or those tables containing the string that you specify in the Enter Bind Variables dialog box (deselect Null in that box to enter a string).

    • Quality Assurance reports: These are table reports that identify conditions that are not technically errors, but that usually indicate flaws in the database design. These flaws can result in various problems, such as logic errors and the need for additional application coding to work around the errors, as well as poor performance with queries at run time.

    • Tables without Primary Keys: Lists tables that do not have a primary key defined. A primary key is a column (or set of columns) that uniquely identifies each row in the table. Although tables are not required to have a primary key, it is strongly recommended that you create or designate a primary key for each table. Primary key columns are indexed, which enhances performance with queries, and they are required to be unique and not null, providing some automatic validation of input data. Primary keys can also be used with foreign keys to provide referential integrity.

    • Tables without Indexes: Lists tables that do not have any indexes. If a column in a table has an index defined on it, queries that use the column are usually much faster and more efficient than if there is no index on the column, especially if there are many rows in the table and many different data values in the column.

    • Tables with Unindexed Foreign Keys: Lists any foreign keys that do not have an associated index. A foreign key is a column (or set of columns) that references a primary key: that is, each value in the foreign key must match a value in its associated primary key. Foreign key columns are often joined in queries, and an index usually improves performance significantly for queries that use a column. If an unindexed foreign key is used in queries, you may be able to improve run-time performance by creating an index on that foreign key.

  • XML Reports

    These reports list information about XML objects.

    • XML Schemas: For each user that owns any XML objects, lists information about each object, including the schema URL of the XSD file containing the schema definition.

26.9 Troubleshooting Database Connections

This section contains information to help you if you have problems connecting to a database.

26.9.1 Deploying to a Database that Uses an Incompatible JDK Version

If you get the following ORA-29552: verification warning: java.lang.UnsupportedClassVersionError when deploying Java to the database you need to change the version of the JDK used for that project to a version compatible with that used by the database.

This version of JDeveloper uses Java JDK Version 1.6.

Table 26-1 JDK Version Used by Different Database Versions

RDBMS Version JDK Version

9.2

1.3.1

10.2

1.4.2_04

11.1

1.5.0_10

11.2

1.5.0_10

For information about changing the Java SE on a project by project basis, see the section on setting the target Java SE in How to Set Properties for Individual Projects.

You can download previous releases of Java SE from http://www.oracle.com/technetwork/java/javase/downloads/previous-jsp-138793.html.