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?
GNOVAKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
0
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.
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.