Oracle 11 PL/SQL - export sql result to excel

Posted on 2014-08-07
Last Modified: 2014-08-29
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?
Question by:GNOVAK
    LVL 76

    Accepted Solution

    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.
    LVL 73

    Assisted Solution

    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
    LVL 23

    Expert Comment

    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.
    LVL 34

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now