Exporting Data With SQL Navigator

A lot of developers use SQL Navigator (also affectionately known as SQLNav) to write their SQL queries and PL/SQL code. With SQLNav you can tune your sql statements and debug your PL/SQL (more details on these will come in future blogs. Stay tuned.)

When you are writing your queries, either for a new application feature or to gather data for one of your end users, you may need to export that data to a format that the requestor can understand and process.

There are several options available under the right-mouse button when you are on a data grid:

Clicking on the ‘Export Data…’ list item will provide you with these options:

You can save this data as an HTML file, Excel spreadsheet (even though it says Excel 97-2010, I have been successful with Excel 2016), or XML; Spool the text to a file, create a Table Insert statement, and create delimited text.

This should cover most of your needs.

This is a sample of an HTML file:

And this is an example of an Excel spreadsheet (even though the dropdown says Excel 97-2010 I was able to save this into Excel 2016):

Now, an interesting feature is the ability to take this output, which came from multiple tables, and create a new table based on this data result set. You probably get asked to do this frequently.

From the Export Data options I chose the Table Inserts option and saved it to a file. This created a text file with insert statements:

As you can see, it inserted a variable of &&table_name in my insert statement. Hmmm, I wanted an actual table name here. What did I do wrong?

I forgot to click on the ‘Options…’ button to bring up the options available to me. Below you can see the various options you have when creating an insert statement.

I forgot to name my new table. Oops.

Now you can see the differences when I add a table name (mark_table) to my output file:

I can now run this file in the editor and create a new table that consists of the output from my previous query. This can be very helpful for running reports on this data set without having to run the query every time to gather the data again.

Hopefully you found this blog helpful, and I encourage you to play with some of the other output options and features in SQLNav to learn more about the power of SQLNav.

You can find more information about SQL Navigator on the Quest.com website.