Using the SQL Developer Export Functionality for Data and Query Results

In this article, I’ll explain how you can use the SQL Developer export functionality.

Export Formats

SQL Developer allows you to export data into a range of formats:

• CSV
• Delimited
• Excel 2003+ (xlsx)
• Excel 95-2003 (xls)
• Excel.xml
• Fixed
• HTML
• Insert SQL
• JSON
• Loader
• PDF
• Text
• XML

Exporting Data

There are a few ways you can export data in SQL Developer.

The first way is to export an entire table. To do this, expand the object explorer until you find the table you want to export.

Right click on the table and select Export

The Export wizard will appear.

In the Export DDL section, choose whether you want to export the DDL or not, and what parts of the DDL.

The DDL (Data Definition Language) will include the information required to create the actual table. Without it, you’ll just get the data.

In the Export Data section, you can choose whether you want to export the actual data or not, and then select a format.

This process can be used just to export the CREATE TABLE statement, or to export the data, or both.

Select the format you want to export to, and new options will appear.

For example, exporting as an “insert” will create an SQL file with INSERT statements for the data, allowing you to re-insert the data from this file.

Select the location where you want to save the file, and click Next.

Now, select the object, columns, and where clause for the data. You may only want to export some of the columns or rows. This is where you will specify that.

Also, you can enter a Global Where, which will be included in all objects you’re exporting.

Click Next.

You’ll see a summary, which shows the information in a tree structure. Expand the trees to see what’s included, and click Finish.

Your export file is then displayed. Depending on your format, you’ll see a different file.

I’ve chosen SQL, so my file is opened in SQL Developer.

So, that’s how you export a table in SQL Developer.

 

Export Entire Database

You can also export your entire database from SQL Developer.

The process is pretty similar to exporting a table.

First, go to Tools > Database Export.

The Export Wizard appears. It looks similar to the one you saw when you exported a table.

Select your connection.

Then, select if you want to export DDL (the scripts to create the tables and other objects), and the preferences to go along with that.

Then, select if you want to export the data as well. Like with exporting tables, you can export either the DDL or the data, or both.

You can also select from a range of formats, as mentioned above.

Then, select your filename, and click Next.

In this example, I’m going to export the database as SQL.

On the next page, select the objects you want to export. For this example, I’ll only export the tables.

On the Specify Objects page, you can add in filters and objects to the export results.

Click Next if you don’t want any filters.

On the Specify Data page, you can add in filters on the data to export.

Click Next if you don’t want any filters.

The summary page will appear. Click Finish if this information is OK, or click Back to make changes.

The file will then be exported and displayed. Your view will depend on the output format chosen. As I have chosen SQL, my SQL file will be created and displayed within SQL Developer.

 

Export Query Results

Exporting query results is another great feature of SQL Developer. It’s useful if you want to do extra analysis for data (something a database developer does occasionally), or if you want to share the results with other people.

To use the SQL Developer export feature on query results, find and run a query that you want to export the results for.

Run it using the Run Statement command so you get a table view (F9).

You could run it using the Run Script command (F5), but the export process is simply a copy and paste.

Right click in the Query Result window and select Export.

The Export Wizard will appear.

Choose your format and filename, and any additional information, and click Next.

For this example, I’m going to export the data as a HTML file.

On the Summary page, review your changes and press Finish.

The file will then be exported.

Browse to the location where you saved the file, and open it.

In this case, I’m opening my HTML file, which looks like this:

 

So, that’s how you use the SQL Developer export functionality to export tables, databases, and query results.

Career Action Tip: Play around with the Export functionality in SQL Developer to find a format that works for you.