Skip to Main Content
  • Questions

  • Write PLSQL procedure to get query result in an excel automatically

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pranami.

Asked: May 14, 2018 - 3:24 pm UTC

Last updated: October 26, 2021 - 2:33 am UTC

Version: Toad for oracle 11g

Viewed 50K+ times! This question is

You Asked

Hi

Though I have checked in google many times for the answer of my question 'Write PLSQL procedure to get query result in an excel automatically', till I am not having clear understanding .

Could you Please explain it in simplest way possible.(like creating 1/2 table with 3/4 columns, then creating a select query and then to automate the output in excel)

I will be using Toad.

many thanks in Advance

and Chris said...

First up, Oracle Database doesn't talk Excel natively. If you want to create an XLS(X) file, you to implement routines to do it. Or borrow from someone else who already has ;)

Such as this example from Anton Scheffer:

https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

It's easiest to write a CSV file instead. You can do this in PL/SQL using utl_file. There are many examples of this on the web, such as:

https://oracle-base.com/articles/9i/generating-csv-files#custom-plsql

Or you can spool the output from a query using SQL*Plus/SQLcl:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=sqlplus-query-output-to-csv-or-txt-format

SQLcl has the benefit of allowing you to specify the output format as CSV.

But if you're looking to load data into Excel from a database, you may be doing this the wrong way around...

In Excel, configure Oracle Database as a data source. Then you can run queries from there to get the data in:

http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

About extracting data from Database to an excel sheet

Dipak, February 25, 2021 - 3:22 am UTC

Is there any program by which we can convert our database data into a excel sheet ..plz give some idea about it.
Connor McDonald
February 25, 2021 - 5:41 am UTC

SQL Developer- can spool to CSV and XLSX
SQLcl - can spool to CSV
SQLPlus - can spool to CSV
Application Express - can spool to CSV and XLSX

Use Apex API

Marcelo, October 23, 2021 - 8:44 pm UTC

You can use apex_data_export to export data to excel !
Connor McDonald
October 26, 2021 - 2:33 am UTC

Good input. Brings this question up to date.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.