So you want to export data from Oracle? Let’s count the ways.

Using Toad® for Oracle, there are many ways to export data from your Oracle databases. What is the fastest way to export data from Oracle? It all depends upon your specific use case. Of course, there is the obvious method of using the Data Pump Export Utility within TOAD but, that may not always be your best option.

Depending upon what you’re doing at the moment, whether it's browsing your tables within the Schema Browser or constructing a query within the Editor or Query Builder, you are only a click away from exporting your data in the context of your work flow.

Let’s take a closer look.

1. How to export data from Oracle in many formats using the Schema Browser

Within your Schema Browser, you can simply left click on a table and select the Data tab as seen below:

Within the data grid, you can right-click and select from the following menu:

You can then select Export Dataset.

While the default output format is Delimited Text, you have many other choices including but not limited to Excel, HTML Table, Insert Statements, JSON, Merge Statements, ODBC Connection (for output going to a non-Oracle database), SQL*LDR and XML.

Take note of the "Export Dataset" feature; you'll be seeing it appear much more as you read on!

You can even Mask portions of the output if you like.

2. How to export data from Oracle by means of creating a complex using Query Builder into various formats

Let’s say your data needs expand beyond a table; you have options for that too.

You can simply right-click on a table within your schema browser and select either ER Diagram or Query Builder as follows:

When you select ER diagram, you are presented with an ER diagram starting from the table you selected connecting to all tables with a Foreign key relationship (to a depth you choose):

You can then select the Query Builder icon (as highlighted above) to see the same diagram with additional capability of choosing columns and more.

Of course, you could have gone directly to the Query Builder as specified above (without having to select the ER Diagram).

Within the Query Builder, you can select your desired columns, where clause (simple or complex), group by and sort columns.

Let’s put a simple Where Clause on the Balance column where it must be greater than 7,600:

Notice how it changes the underlying query:

Let’s run this query to see the resulting data grid:

Within this data grid, you can simply right-click and chooseExport Dataset with all the options as previously explained. So now you see how you can create a complex query and export the resulting data into various formats.

3. How to export data from Oracle for a group of tables regardless of their parent-child relationship

Your use case may require you to export the data for a group of tables regardless of their parent-child relationship. You can select the desired tables by simply shift or control clicking on them, right-click and then select Export Dataset. Again, you’ll have the all the same options as mentioned above.

Those are just the options available through the Schema Browser. You also have options through the Editor.

4. How to export data from Oracle Toad for Oracle's Editor

No matter how complex your query is, you can run it and see the resulting data grid within Toad for Oracle.

I think you know what’s coming next.

Yes — you can right-click within the data grid and select Export Dataset, again with all the capabilities as previously mentioned.

So you see there are many ways to export your data from Oracle. Which one you choose all depends upon your work flow and the amount of data you want to export.

And don't forget … 

Or should I say, last but not least …

5. How to export data/metadata using Toad for Oracle's Data Pump Wizard

You can always go to the Database > Export pulldown to be presented with more choices. In addition to exporting application data, you may need to export metadata (and in various formats).

By choosing Data Pump Export, you’ll be presented with an easy to use wizard:

You can choose from Tables, Schemas, Tablespaces, Database or Transportable Tablespaces Modes. Here, we’ll select Schemas. You can choose your Schemas (under items), Use a query, filter in our out objects, choose your data pump export parameters and where the export will go (under files).

And guess what? This can all be automated through Toad for Oracle's Automation Designer!

If your data export needs extend beyond Oracle, there is always Toad Data Point where you can connect to almost 40 data sources including any source that connects via ODBC! You can even join data from different platforms.

I hope you found this blog useful and have discovered the many different options for exporting Oracle data and more!

Read more blogs from Jeff.

Related information

Forum: Toad and XML 

Forum: Automate Export Data To Excel Using SQL Script

Blog:Toad® for Oracle 13.2 new release: Unit test options

Have questions, comments, or an idea to share?

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!