FB

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.

export 01

Right click on the table and select Export

export 02

The Export wizard will appear.

export 03

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.

export 04

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.

export 05

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.

export 06

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.

export 07

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

export 08

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.

export 09

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

export 10

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.

export 11

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

Click Next if you don’t want any filters.

export 12

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 13

export 14

 

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.

export 15

Right click in the Query Result window and select Export.

export 16

The Export Wizard will appear.

export 17

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:

export 18

 

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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Download the SQL Cheat Sheets: common commands and syntax - to save you time.
You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.
Print them or use them as an easy reference.