Importing and Exporting using the Oracle SQL Developer 3.0

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to import and export your database objects using Oracle SQL Developer 3.0.

Time to Complete

Approximately 20 minutes.

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, users can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Default connectivity to the database is through the JDBC Thin driver, hence an Oracle Home is not required. To install SQL Developer, simply download and unzip the file. SQL Developer users can connect to any supported Oracle Database.

Oracle SQL Developer 3.0 introduces a DBA Navigator and provides support for DBMS Scheduling. Other features include a new Query Builder, SQL Tuning Advisor, and Explain Plan Diff utility. For a complete list of new features, click here.

This tutorial focuses on Import and Export functionality.

Prerequisites

Before starting this tutorial, you should:

.

Install Oracle SQL Developer 3.0 from OTN. Follow the release notes.

.

Install Oracle Database 11g with sample schema.

.

Unlock the HR user. Login to SQL Developer as the SYS user and execute the following command:
alter user hr identified by hr account unlock;

.

Download and unzip the files.zip file that contains all the files you need to perform this tutorial.

Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer 3.0 is to create a database connection. To create a connection, perform the following steps:

.

If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer session. If you do not have the icon located on your desktop, find the executable file (either named "sqldeveloper.exe" on Windows or "sqldeveloper.sh" on Linux), and run it.

 

.

Your Oracle SQL Developer application starts.

 

.

In the Connections navigator, right-click Connections and select New Connection.

 

.

The New / Select Database Connection dialog window opens. Enter the connection details as follows:

Connection Name: hr_orcl
Username: hr
Password: <your_system_password>
Hostname: <your_machine>
SID: <your_SID>

Click Test.

 

.

If the status is success, click Save to save the connection, and then click Connect.

 

.

The connection is saved and you see the connection in the Connections list.

Configuring the Import Environment

You can use the Import Wizard to customize your preferences for the import environment and import data using the different methods like Insert, Insert Script, External Table, Staging External Table and SQL*Loader.

Perform the following steps to set the preferences in your Import Wizard.

.

Select Tools > Preferences.

 

.

In the preferences navigator, expand Database and select Import.

Here you can specify default properties for import. Set the Import Row Limit to 100 to be able to view 100 rows at a time. This helps while working with large files. Set a value for the Preview File Read Maximum. This ensures that the entire file is not read into the memory.

 

.

Expand File Formats in the preferences navigator. There are four types of file formats. You can specify default attributes for each supported format for the imported data.

 

.

For example, select the Delimited Format to set the default preferences.

 

.

Select Header to display the first row of data as the header, if this option is not selected then the data is displayed without a header. Then select the left and right enclosure as '[' and ']' respectively.

Follow the same steps to set the default preferences for the other file formats.

 

.

The Import Methods pane allows you to specify the default mode for importing data into the existing table or a new table.

Select Import Methods in the preferences navigator.

 

.

The five methods used to import data to a new table are:

Insert: Imports the data directly into the table.

Insert Script: Creates a script with insert statements.

External Table: Generates External Table DDL for reading the file.

Staging External Table: Creates a temporary External Table for Importing into a table.

SQL Loader Utility: Generates files for SQL Loader to Import a table.

Select New Table Import Method as Staging External Table from the list.

 

.

Select External Table in the preferences navigator.

Do not specify any values here. The default directory will be used. Click OK.

Note: If you want to specify a default directory, make sure that the appropriate permissions are granted.

 

.

Select Insert in the preferences navigator and set the Insert Batch Size (Rows) as 50.

 

.

Select SQL*Loader in the preferences navigator and specify the log and bad files directories here. If the directories are not specified, by default they will be written to the Import file directory.

 

.

Click OK to save your default preferences.

 

Importing Data into a Table using Insert Method

Oracle SQL Developer makes it very easy to import data into an existing table.

Perform the following steps to import data into the DEPENDENTS table using Insert Method.

.

Before importing the data into a table, make sure you have the table with the specified column.

Expand Tables and select DEPENDENTS.

 

.

Click Data.

 

.

Observe that there is no data in the DEPENDENTS table.

 

.

To import the data to the DEPENDENTS table from another location, right-click DEPENDENTS and select Import Data.

 

.

Select the load_dep.dsv file from the directory where you unzipped the files from the Prerequisites section and click Open.

 

.

Click Next.

 

.

Select Import Method as Insert and click Next.

 

.

 

In the Choose Columns window, click Next.

 

.

In the Column Definition window, select Name from the Match By drop down to match the Source Data and Target Data by Name.

 

.

In the Target Table Columns click the Name drop down and make sure that the Source Data Columns and the Target Table Columns are mapped correctly.

 

.

Click Next.

 

.

Click Verify to verify the parameters before importing.

 

.

Note that the test status is SUCCESS.

Click Finish.

 

.

Click OK.

 

.

To view the data, make sure the Data tab is selected and click Refresh to show all the data.

 

Configuring the Export Environment

You can use the Export wizard to export some or all database objects of a database connection to a file. The export file will not only contain the SQL data definition language (DDL) statements to create the exported objects, but if you choose to export the data too, the export file will contain Data Manipulation Language (DML) to populate the exported objects.

Perform the following steps to set your default environment in the Export Wizard.

.

Select Tools > Preferences.

 

.

Expand Database in the preferences navigator.

 

.

Expand the Export/View DDL Options to display all the file format types.

 

.

You can change default values for the source and target pages in this export wizard.

Select Export/View DDL Options.

 

.

Select Format as insert from the drop down. Insert exports the data in SQL format.

 

.

Select Save As as Single File to export data into a single file. Click OK to save your settings.

 

Export the Database

In this topic, you learn to Export your full database into the required file format.

.

Click Tools > Database Export.

 

.

In the Connection drop down, select the connection from which you want to export objects. In this example you choose the connection hr_orcl.

 

.

Deselect the Export Data check box and Click Next.

 

.

Retain the default values of all object types selected. Click Next.

Note: You must select at least one object.

 

.

Click Next.

 

.

You can review the information that is used to create the output file. The Export Summary information will contain statements to export database objects and data according to your specifications. To create the output file, click Finish.

 

.

Since the preferences was set to open as SQL file, observe that after exporting, the export.sql file automatically opens in SQL Worksheet. All the DDL statements in the 'hr_orcl' schema are displayed.

You can scroll down to view the entire data.

 

.

You can also export data using Connection Navigator. Perform the following steps:

Select hr_orcl in Connections navigator.

 

.

Expand Tables and right-click EMPLOYEES > Quick DDL > Save to Worksheet.

 

.

The DDL for the EMPLOYEES table is displayed. Scroll to the bottom of the window to view the entire data.

 

Exporting from the Data Grid

In this topic, you invoke the export wizard from the Data Grid, filter the data, select only the required columns and export the data.

.

Select the EMPLOYEES table.

 

.

 

Select Data from the tab.

 

.

To Export the data, right-click anywhere in the data grid and select Export from the context menu.

 

.

You can export the data in different formats. For our example, select Format as pdf from the drop down list.

 

.

Enter the following values:

Title: Emplist

Subject: Employees list

Keywords: Emp list

Click Next.

 

.

Click Columns.

 

.

Select the Data Columns, which you want to export. By default, all the object types are selected. To select or deselect all the object types, click Toggle All check box.

 

.

In this example you choose the columns EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY and deselect the others. Click OK.

 

.

To apply filters, click Where ( ).

 

.

Enter Where clause as SALARY > 10000 and click Go.

 

.

Observe that all the employees whose salaries are greater than 10000 are displayed. Click OK.

 

.

Click Next.

 

.

Review Export Summary and click Finish.

 

.

If you review the export.pdf file, you should see the following:

 

Summary

In this tutorial, you have learned how to:

Credits

Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved