Whether you are invoking Export Data for a particular table in the Connections navigator or from the table's data grid, the default output includes all of the columns and data. This is also true if you have run one of the shipped SQL Developer reports. If you want to export any subset of data from any of these or the other data grids available, you can change the number of rows of data exported, or even the columns in the grid, using the Export Data dialog to restrict the export detail. The first choice is selecting the columns that you want to export:

Once you have the columns selected, you can also restrict the data returned. Add the WHERE CLAUSE restriction criteria and click on Go to restrict the data returned.

Instead of using the dialog, if you set up the query in the SQL Worksheet, you'll need to write the query to ensure it contains exactly the data that you want to export. Indeed, some file formats, such as XLS, have a maximum records file size. Therefore, you're advised to limit the data you want to export. Once the data is prepared and ready, you can simply invoke the context menu to export the data. Select the file format and location, and run the export.

Creating an SQL script file of insert commands is very useful when creating sets of test data. You can then modify and update the data at will and then rerun the script to restore the data to the initial state. To create the SQL DML commands, go to Export Data | Insert from the context menu.

If you export this format to the clipboard, after that open an SQL Worksheet and paste (Ctrl+V); you are then in an ideal position to run the script. Ensure that you provided the correct table name in the Export Data dialog. If, for example, you write an SQL query and then invoke the Export Data dialog, the Table field is "table_export", so you'd need to update that field as needed.

You can export the data to the HTML format. To create the HTML, go to Export Data | HTML from the context menu. Once created, you can store the file on a central server and display it in a browser. This is very useful when you need to share data with other users, without providing any update permissions. The exported HTML file includes a basic search box, allowing users to further restrict the records displayed. In the following screenshot, the file shown is open in a browser:

SQL Developer supports exporting data to an SQL*Loader file. Go to Export Data | Loader from the context menu to create the SQL*Loader file. Once created, you can use SQL*Loader to load the data into the Oracle database. The file format is set by SQL Developer, so you don't have the option to set parameters to control the Loader file format.

To create a working example, enter the following query into the SQL Worksheet and use the steps listed next:

select E.FIRST_NAME,
  E.EMAIL ,
  E.HIRE_DATE ,
  E.LAST_NAME,
  D.DEPARTMENT_NAME
from EMPLOYEES E,
  DEPARTMENTS D
where E.DEPARTMENT_ID = D.DEPARTMENT_ID;

The default control file created in SQL Developer, based on the example that we have just seen, is structured as follows:

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE "EMPLOYEES"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
FIRST_NAME,
EMAIL,
HIRE_DATE timestamp "DD-MON-RR",
LAST_NAME,
DEPARTMENT_NAME)
begindata
Whalen|Jennifer|JWHALEN|17-SEP-87|Administration
Hartstein|Michael|MHARTSTE|17-FEB-96|Marketing
Fay|Pat|PFAY|17-AUG-97|Marketing

SQL Developer supports the export of data to the Microsoft Excel (XLS) format. This is one of the more popular export formats. The data, once in a Microsoft Excel spreadsheet, is easily used for graphs and statistical review. To create the Microsoft Excel file, go to Export Data | xls from the context menu. In this instance, the Table field in the Export Data dialog is not available. You only need to provide a filename and make the columns and Where clause choices. The new file, when opened in Microsoft Excel, also includes the column headings.

It's important to be aware of the fact that there are limitations when working with large sets of data. There is a 65,536 row limit in Microsoft Excel, so SQL Developer writes the records to new worksheets when 65,000 is reached. In addition, working with an XLS file, SQL Developer loads all of the records into memory before writing. If the XLS file is significantly large, you can run out of memory. If this is an issue, then edit the sqldeveloper.conf file in the <YourDirectory>sqldevelopersqldeveloperin directory and increase the heap size by increasing the default value set to AddVMOption -Xmx1024M.

The SQL Developer Data Export also supports exporting to XML. To create an XML file, based on the data in the grid, go to Export Data | xml using the context menu. A single row of data (in this case from the EMPLOYEES table) exported to XML follows a structured format as follows:

<ROW>
  <COLUMN NAME="EMPLOYEE_ID"><![CDATA[100]]></COLUMN>
  <COLUMN NAME="FIRST_NAME"><![CDATA[Steven]]></COLUMN>
  <COLUMN NAME="LAST_NAME"><![CDATA[King]]></COLUMN>
  <COLUMN NAME="EMAIL"><![CDATA[SKING]]></COLUMN>
  <COLUMN NAME="PHONE_NUMBER"><![CDATA[515.123.4567]]></COLUMN>
  <COLUMN NAME="HIRE_DATE"><![CDATA[17-JUN-87]]></COLUMN>
  <COLUMN NAME="JOB_ID"><![CDATA[AD_PRES]]></COLUMN>
  <COLUMN NAME="SALARY"><![CDATA[24000]]></COLUMN>
  <COLUMN NAME="COMMISSION_PCT"><![CDATA[]]></COLUMN>
  <COLUMN NAME="MANAGER_ID"><![CDATA[]]></COLUMN>
  <COLUMN NAME="DEPARTMENT_ID"><![CDATA[90]]></COLUMN>
</ROW>

Exporting data to XML is very beneficial in broader application development, since the structure of the XML file enables you to use this data in other applications. It may be more common to export the metadata for reuse than the instance data in this case.

If you're working with the default settings provided by SQL Developer, selecting the SQL display editor for a table reveals an involved CREATE TABLE statement, as shown in the following screenshot:

Selecting the SQL display editor is an easy way to get the DDL for a single table. Selecting Export DDL from the context menu in the Connections navigator provides the additional capability of generating the DDL for one or more tables at a time. For the DDL export, select the table(s) and write the DDL to file, clipboard, or directly to the SQL Worksheet:

The structure of the DDL in the Export DDL menu is driven by the same preferences that shape the SQL in the SQL display editor: Database | ObjectViewer.

The Database Export follows a series of steps and choices, allowing you to choose the object types, the specific objects, and to select or restrict the data exported. Go to Tools | Database Export… to invoke the wizard.

The outcome of the Database Export is a file containing DDL and possibly DML statements. The DDL statements are almost all CREATE object statements, although you can also include GRANT statements, and the DML comprises INSERT statements. You can control the structure of the statements in the first stage by setting different options. Set the file location and the schema driving the export on this first page.

The DDL is again derived from executing procedure calls to the DBMS_METADATA package. Most of the options set on this first page are parameters sent to this package, except some, such as the Include Drop Statement. In the preceding screenshot, the lowest three statements are not set by default.

By selecting Include Drop Statements, you can create a script that is useful in a test environment. By including the DROP commands, you can rerun the script repeatedly, by first dropping the tables, then creating them, and then inserting the data. Oracle supplies many similar scripts. demobld.sql, which creates the EMP and DEPT tables, is one such example.

Although many of the scripts run are for a single schema, you can also create scripts for a number of schemas by including the Include Grants option. The wizard reads and replicates the current object privileges in the schemas. If you are selecting objects from multiple schemas, you need to include Show Schema to ensure the appropriate schema prefixes each object created.

Once you have set the options driving the DDL, you need to select the objects for generation. By default, all of the objects are selected, including Data. This means you can select Proceed to Summary and then Finish, and the DDL and DML for the full schema and data will be created. To restrict the choice of objects, deselect the Toggle All checkbox, which then gives you the option of selecting individual items by allowing you to just select only those object types you want to appear in the file. On the next page of the wizard, you select the specific, named items.

Select Data to ensure that the DML for the selected tables is also created.

The details in step 3 of the wizard are driven by the choices made on the previous pages. The schema selected controls the objects you have access to, both in the schema itself and across other schemas. You only need to select a starting schema, such as SCOTT, or another schema with very few privileges, to see how few tables are available.

The HR sample schema can query and therefore can produce the DDL for some of the HR tables. In the screenshot that follows, the four areas marked are as follows:

Taking the example we have been working on in the wizard, the output is sent to the SQL Worksheet as a single file that you can run. Notice that the start of the script includes a DROP statement for all objects to be created, in this case, tables and sequences. Once these have been dropped, the path is clear to create new objects, and then insert the data. The connections drop-down list on the right-hand side makes it easier to select an alternate schema to run the script against.