Home / Middleware / Oracle JDeveloper

26 Connecting to and Working with Databases

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

This chapter includes the following sections:

• Section 26.1, "About Connecting to and with Working with Databases"

• Section 26.2, "Configuring Database Connections"

• Section 26.3, "Browsing and Searching Databases"

• Section 26.4, "Connecting to Databases"

• Section 26.5, "Connecting and Deploying to Oracle Database Cloud Service"

• Section 26.6, "Importing and Exporting Data"

• Section 26.7, "Copying, Comparing, and Exporting Databases"

• Section 26.8, "Working with Oracle and Non-Oracle Databases"

• Section 26.9, "Working with Database Reports"

• Section 26.10, "Troubleshooting Database Connections"

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 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 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 Express Edition."

26.2 Configuring Database Connections

You can define and manage connections to external data sources using the Create Database Connection dialog. Database connection are shown in:

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

• The Databases window, where you can create, edit, and modify objects in the database.

• Application Resources panel in the Applications window, 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 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.

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 Databases window, and an additional node in either the Resources window or in the Application Resources panel of the Applications window depending on the scope of the connection.

Team level database connections are also available when JDeveloper is configured to work with teams.

In the Applications window and Databases window, you can expand the database connection node to view and work with database objects. In the Resources window, 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 Databases window, the node for the connection is under the node with the same name as the application.

In the Applications window, 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 Applications window to use it in an application by:

• From the Resources window, dragging the connection and dropping it on the Connections node in the Applications window under Application Resources.

• From the Resources window, right-clicking the connection and choosing Add to Application.

• In the Databases window, dragging the connection 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

To create a database connection:

1. If necessary, choose Window > Database > Databases. 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.

After you have defined a connection, you can return to the dialog and edit its attributes, however you cannot change the connection type after the database connection has been created.

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 Window > Database > Databases.

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 import and export database connections, described below.

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 Window > Database > Databases.

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 Resources window. For more information, see Section 3.7.4, "Importing and Exporting Catalogs and Connections."

26.2.7.2 Importing Database Connections

You can import connection descriptors that have previously been exported.

To import a database connection:

1. Choose Window > Database > Databases.

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 Resources window. For more information, see Section 3.7.4, "Importing and Exporting Catalogs and Connections."

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 Window > Database > Databases.

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 Window > Database > Databases.

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 Databases window 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 application or project. It is best to use caution when deleting connections.

To delete a database connection:

1. Choose Window > Database > Databases.

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 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 Databases window.

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 Databases window, 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 Applications window.

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 Databases window.

• To view offline database objects, use the Applications window.

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

To open the Applications window or Databases window:

1. Choose Window from the main toolbar.

2. To open:

   • The Applications window, choose Applications.

   • The Databases window, choose Database > Databases.

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 > Databases window.

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 Applications window to find objects such as offline tables or views.

To view offline schemas and the objects they contain:

1. In the Applications window, 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 Databases Window

To use filters:

1. Choose Window > Database > Databases.

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 Databases Window".

   With the object still selected, click the Apply Filter button in the Databases window 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 Window > Database > Databases.

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 Databases window toolbar, click the Apply Filter button in the Databases window 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 Databases window 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 Databases window.

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 Window > 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 window.

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:

• Databases window.

• Applications window.

• Resources window.

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 a number of Oracle and non-Oracle databases.

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 Java DB/Apache Derby

Apache Derby is an open source relational database implemented entirely in Java. Java DB is Oracle's supported distribution of the Apache Derby open source database. 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. For more information about Java DB, see http://www.oracle.com/technetwork/java/javadb/overview/index.html.

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 Databases window. Instead columns and constraints are displayed in the Structure window when the table is selected in the Databases window.

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 Connecting and Deploying to Oracle Database Cloud Service

You can connect to and deploy Oracle Database objects to Oracle Database Cloud Service. For more information about Oracle Database Cloud Service, see https://cloud.oracle.com.

26.5.1 Types of JDeveloper Connection to Oracle Database Cloud Service

JDeveloper connects to an Oracle Database Cloud Service instance using two types of connection:

• Database Connection: This connection is made using the Service Home URL (identified in the "Welcome to Oracle Cloud" email). This URL connects to Oracle Application Express and you can browse database objects and deployments on your Oracle Database Cloud Service instance from a node in the Databases window. The connection is not validated until you connect to the Oracle Database Cloud Service instance, when you are prompted to provide a password.

• Secure FTP connection: The Secure FTP hostname and SFTP user identified in the "Welcome to Oracle Cloud" email are used in this connection to upload the deployment ZIP file to your Oracle Database Cloud Service instance using SFTP. At the Oracle Database Cloud Service end, there is a background job which runs to download these files and put them onto the Oracle Database Cloud Service instance.

  Once you have entered the Secure FTP hostname and user, either in the Create or Edit Cloud Connection Dialog or the Deploy Objects to Cloud Dialog, the information is saved and will be present the next time you deploy objects to your Oracle Database Cloud Service instance.

  If you have just signed up for Oracle Database Cloud Service, there are some steps you have to carry out before trying to create a connection from JDeveloper. For example, the Identity Domain Administrator has to create new passwords for the Service Home and for the Secure FTP Site. The information you need is in the "Welcome to Oracle Cloud" email.

  Also, you have to create users who can create database connections to Oracle Database Cloud Service instances. For more information, see "Managing Service Users" available as part of the documentation available from the Resources menu at https://cloud.oracle.com.

26.5.1.1 Creating an Oracle Database Cloud Service Connection

After you sign up for the Oracle Database Cloud Service, you will receive the connection information you need.

How to create a connection:

1. If necessary, open the Databases window by choosing View > Database > Databases Window.

2. Either click the New Connection button, or right-click the Cloud Connections node and choose New Cloud Connection.

3. In the New Cloud Connection dialog, enter the following information. For more help at any time, press F1 or click Help.

4. • A name for the connection

   • The username of a user able to make a connection to Oracle Database Cloud

   • The Service Home URL

   • The Secure FTP username

   • The Secure FTP hostname

The connection is created and listed in the Databases window, but the information is not validated until you connect to Oracle Database Cloud Service.

26.5.1.2 Editing an Oracle Database Cloud Service Connection

You can only edit a connection when it is disconnected.

How to create a connection:

1. If necessary, open the Databases window by choosing View > Database > Databases Window.

2. Right-click the node for the Cloud connection under the Cloud Connections node and choose Properties.

3. IIn the Edit Cloud Connection dialog, make the changes that you want. For more help at any time, press F1 or click Help.

26.5.1.3 Connecting and Disconnecting from an Oracle Database Cloud Service Connection

How to connect:

1. If necessary, open the Databases window by choosing View > Database > Databases Window.

2. Expand the the Cloud Connections node and either:

   • Click on + next to the Cloud connection node.

   • Or right-click the Cloud connection node and choose Connect.

3. In the Authentication dialog enter the password for the Cloud connection user. For more help at any time, press F1 or click Help.

The Cloud connection node expands to show the databae objects present in the Oracle Database Cloud Service instance, and below those nodes previous deployments to the Oracle Database Cloud Service instance.

If an error message is displayed, check the connection properties.

How to disconnect:

• Expand the the Cloud Connections node, right-click on the Cloud connection node and choose Disconnect.

26.5.2 Using the Database Cart

The Cart is a convenient tool for collecting Oracle Database objects from one or more database connections, and deploying, exporting, comparing, or copying those objects. You can put objects into one or more carts, each with its own tab. When the Cart window is opened, it contains an empty cart, although you can create new carts and open previously saved carts in new or existing cart tab.

Objects in the Cart are not automatically synchronized with database activity; to update the contents of the cart with the current state of the database, click the Refresh icon. If an object does not exist after a refresh, the object is disabled in the Cart and is underlined to indicate the error.

You can put database objects into a cart tab in several ways:

• Drag and drop objects from the Databases window into the Cart window.

• Select one or more objects in the Databases window, right-click, and select Add to Cart.

• Open a previously saved Cart XML file.

• Add scripts By using the Scripts icon drop-down (Add Initial Script, Add Final Script). (If you use a Cart tool that does not support scripts, they are ignored.)

26.5.2.1 Setting Database Cart Preferences

You can specify a number of preferences that govern how the cart works, or the defaults used by the cart.

To set the cart preferences:

1. Choose Tools > Preferences.

2. From the Preferences page, navigate to the Database: Utilities: Cart and Database: Utilities: Cart: Cart Deploy pages and make the appropriate changes. For more information at any time, press F1 or click Help from within the dialog.

26.5.2.2 Configuring Database Cart Tools

For each of the Cart database utilities (Export, Diff [compare], Copy), you can create, save, and open utility-specific configuration settings.

You can change the default locations used by the cart in the Database: Utilities: Cart page of the Preferences dialog (available from the Tools menu).

To create and save configuration settings:

1. From the database cart dialog (either Export, Database Diff, or Copy, click the Save Configuration button.

2. In the Save Tool Configuration dialog, the default location for the configuration file is shown. If necessary, browser to a different location.

3. Click Apply.

A configuration file is created at the location you have selected with a name that reflects the tool you invoked the dialog from:

• export_cart.xml

• diff_cart.xml

• copy_cart.xml

To use previously saved configuration settings:

1. From the database cart dialog (either Export, Database Diff, or Copy, click the Open Configuration button.

2. In the Open Tool Configuration dialog, the appropriate configuration file at the default location is displayed:

   • export_cart.xml

   • diff_cart.xml

   • copy_cart.xml

   If necessary, browse to a different location and click Apply.

26.5.2.3 Deploying to Oracle Database Cloud Service

This section describes how to deploy to the Oracle Database Cloud Service. As well as just deploying database objects, you can specify a script to run before and another to run after the generated scripts.

To deploy database objects to Cloud:

1. If necessary, create a Cloud connection.

2. If necessary, set database cart preferences appropriately.

3. If necessary, open the Database Cart. Choose View > Database > Cart. For more information at any time, press F1 or click Help from within any of the windows or dialogs.

4. If necessary, open the Databases window by choosing View > Database > Databases Window.

5. In the Databases window, expand the database connection or connections containing the database objects you want to add to the cart. Select the objects (hold down Ctrl to select more than one at a time) and drag them to the cart.

   You can only use database objects in database connections. You cannot use offline database objects in a Cloud connection.

6. In the Database Cart choose which objects you want to deploy, and if appropriate the data you want to deploy too.

7. In the Database Cart, click Deploy to Cloud and in the Deploy Objects to Cloud dialog choose the options you want, then click Apply.

   You can change the default deployment location in the Utilities: Cart page of the Preferences dialog (available from the Tools menu)

8. If the Cloud connection is not currently connected, the Authentication window appears where you enter the password for the username associated with this connection.

JDeveloper creates the ZIP file at the location you specified in the Deploy Objects to Cloud dialog, then connects to the Oracle Database Cloud Service instance by Secure FTP and transfers the ZIP file containing the deployment files.

You can examine the status of the deployment in the Log window. After deployment, you will be able to see the deployed database objects under the Cloud Connections node in the Databases window.

26.5.2.4 Deploying to a Database Deployment File

You can create a ZIP file containing the DDL and optionally the data for database objects from one or more database connections.

JDeveloper creates a ZIP file which contains:

• A number of SQL scripts containing DDL for the database objects.

• Scripts to deploy any associated data that you want to deploy.

• A master script that has a name of the form Generated-yyyymmddhhmmss.sql.

Once the ZIP file has been created, you can use it to deploy the database objects to a database connection by running the master script.

To deploy database objects to a ZIP file:

1. If necessary, open the Database Cart. Choose View > Database > Cart. For more information at any time, press F1 or click Help from within any of the windows or dialogs.

2. If necessary, open the Databases window by choosing View > Database > Databases Window.

3. In the Databases window, expand the database connection or database connections containing the database objects you want to add to the cart. Select the objects (hold down Ctrl to select more than one at a time) and drag them to the cart.

4. In the Database Cart choose which objects you want to deploy, and if appropriate the data you want to deploy too.

5. In the Database Cart, click Deploy and in the Deploy Objects dialog choose the options you want, then click Apply.

JDeveloper creates the ZIP file at the specified location.

You can change the default deployment location in the Utilities: Cart page of the Preferences dialog (available from the Tools menu).

26.5.2.5 Saving a Database Cart

You can save the contents of the database cart to reuse later.

To save the contents of a cart:

1. If necessary, open the Database Cart. Choose View > Database > Cart. For more information at any time, press F1 or click Help from within any of the windows or dialogs.

2. If necessary, open the Databases window by choosing View > Database > Databases Window.

3. In the Databases window, expand the connection or connections containing the database objects you want to add to the cart. Select the objects (hold down Ctrl to select more than one at a time) and drag them to the cart.

4. Click Save Cart on the Database Cart toolbar and in the Save Cart dialog, check the location and click Apply.

JDeveloper saves the contents of the cart as an XML file at the specified location.

26.5.2.6 Opening a Saved Database Cart

You can open the saved contents of a database cart. You can either empty the cart so that it will contain just the contents of the saved cart, or add them to the existing contents of the cart.

1. If necessary, open the Database Cart. Choose View > Database > Cart. For more information at any time, press F1 or click Help from within any of the windows or dialogs.

2. Click Open Cart on the Database Cart toolbar. For more information at any time, press F1 or click Help from within any of the windows or dialogs.

3. If there are objects already present in the Cart, a dialog asks whether you want to remove current objects from the cart before opening the saved cart.

   Yes empties the current cart and fills the cart with the objects from the cart you are opening.

   No does not empty the current cart, but adds the objects from the cart you are opening to the current cart contents. If one or more objects is a duplicate of the current cart contents it is not added (only one version of an identically named object can be present in a cart).

4. In the Open dialog, navigate to the saved XML file and click Open.

26.5.2.7 Examining Deployments in an Oracle Database Cloud Service Connection

You can examine deployments to an Oracle Database Cloud Service instance.

To examine a deployment:

1. If necessary, open the Databases window by choosing View > Database > Databases Window.

2. Under the Cloud Connections node expand the Cloud connection node you want and then expand the Deployments node.

3. Double-click the deployment you want to examine. The Deployment Object Viewer window opens.

   • The Details tab shows information about the deployment.

   • The Log tab shows log information about the deployment.

To see information that is larger than the cell, hover the mouse over the cell. Alternatively, you can double-click the cell. The contents of the cell are displayed in a Details window. This is particularly useful for information like SQL*Loader files.

You can re-run a deployment, delete the log for a deployment, or delete a deployment from the Oracle Database Cloud Service instance.

To re-run a deployment:

• Right-click the deployment and choose Restart. The scripts contained in the original deployment are run again.

To clear the deployment logs:

• Right-click the deployment and choose Clear Logs. The information in the Log tab of the Deployment Object Viewer is deleted.

To delete a deployment:

• Right-click the deployment and choose Delete. The deployment is removed from the list of deployments.

Note:

This does not remove the database objects deployed to the Oracle Database Cloud Service instance. To do this, specify a deployment which uses a Before Script which contains the appropriate DROP statements for the objects you want to drop and deploy that.

26.6 Importing and Exporting Data

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

Note:

You cannot import data 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:

• An 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.6.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.

For more information, see "SQL*Loader Concepts" in Oracle Database Utilities.

26.6.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.

For more information, see "External Table Concepts" in Oracle Database Utilities.

26.6.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 Window > Database > Databases to open the Databases window.

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.

26.6.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 Window > Database > Databases to open the Databases window.

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

3. Expand the node for the database connection and the schema. Right-click the Tables node and choose Import Data.

4. 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.6.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 Window > Database > Databases to open the Databases window.

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.6.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 and the schema. Right-click the Tables node and choose Import Data and in the Open dialog enter or browse to the location of the file.

3. Click OK to launch the Data Import Wizard.

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

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

5. 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.6.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. For more information, see "SQL*Loader Concepts" in Oracle Database Utilities.

• 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.6.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 Window > Database > Databases to open the Databases window.

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.7 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.7.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.7.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 https://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.7.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.8 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.9 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.9.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 window 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.9.1.1 How to Run Database Reports

The Database Reports window 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 window. In the main menu, choose Window > 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.9.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 window. In the main menu, choose Window > 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.9.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 window. In the main menu, choose Window > 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.9.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 window. In the main menu, choose Window > 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.9.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 window. In the main menu, choose Window > 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.9.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 window. In the main menu, choose Window > 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.9.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 window. In the main menu, choose Window > 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 Window > Database > Database Reports to open the Database Reports window. The shared report is listed under the Shared Reports node in the Databases window.

26.9.2 Reference: Pre-Defined Database Reports

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

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.10 Troubleshooting Database Connections

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

26.10.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.

For information about the JDK, see "JDeveloper Certification Information" at http://www.oracle.com/technetwork/developer-tools/jdev/documentation/index.html.

For information about the JDK used by the database, consult your database documentation.

For information about changing the Java SE on a project by project basis, see the section on setting the target Java SE in Section 4.3.11, "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.