• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2393
  • Last Modified:

Oracle 11 PL/SQL - export sql result to excel

I'm using TOAD for Oracle and it has a interface that allows me to indicate a speadsheet, an instance(tab) name and a few other items to export the results of a query directly to excel.

I have a few SQL statements that I wish to run from PL/SQL and have them automatically exported to an Excel file with a given filename and tab name.
I will then schedule this procedure to run using TOAD scheduler.
At least I think that's the most straightforward way to schedule a query to run with the results exported to an excel file.

Anyone have any ideas on how do export the results from a query to a specific excel file/tab from PL/SQL?
3 Solutions
slightwv (䄆 Netminder) Commented:
Off the top of my head, best bet is to use UTL_FILE and write out the data to a CSV.

Then use Excel to load the CSV.
Toad's spreadsheet export is for SQL statements that Toad runs itself.
 So, if PL/SQL block runs the queries, Toad won't know about it.

CSV will work to create a faux spreadsheet that Excel could import but won't support multiple tabs.

To do Tabs from PL/SQL, you'll need some 3rd party pl/sql package or java library with pl/sql interface
DavidSenior Oracle Database AdministratorCommented:
Likewise, SQL*Developer has a few masks it can offer to re-display a result set.  And when you mention TOAD scheduler I have to presume that the Oracle Scheduler is what's actually used.

This dinosaur is clueless how you expect to generate a "given filename and tab name" in your database query......

My vote, therefore, is the tried and true method of writing a CSV (comma-delimited) local file -- and separately starting up Excel to read in said file.  You could open Excel with a batch/CLI file, and pass the CSV name to load on startup -- but I would steer clear of calling that .BAT file from within PL/SQL.
Mark GeerlingsDatabase AdministratorCommented:
It is easy in Oracle to have a PL\SQL procedure create either a fixed-length or comma-separated ASCII text file (if you use the procedures in the supplied package: UTL_FILE) and it is relatively easy in Excel to open an ASCII file (either fixed-length, or comma-separated) and have Excel convert that for you to Excel's native file format.  And, it is easy to schedule a PL\SQL procedure to run at whatever time or interval you want with either DBMS_JOBS or DBMS_SCHEDULER.

Unfortunately, these files will be created on the database server though, or on a file system or directory that is available to the database server.  This is not on a client machine.  But depending on your network, this could be on a shared storage device that is available to both your database server and your clients.

Another option would be to configure your Excel client for an ODBC-based connection to the database, write your query in Excel, and have Excel run the query and put the results directly into an Excel file.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now