Export Data into Excel from Oracle Table Using PL SQL

Exporting data from an Oracle table directly into an Excel file can be accomplished easily using PL/SQL and the xlsx_builder_pkg package. This utility allows you to generate Excel .xlsx files directly from SQL queries run in your Oracle database. Below, I will guide you through the process step by step.

Downloading and Installing the xlsx_builder_pkg Package

The xlsx_builder_pkg package is an open-source utility that you can download from GitHub. To get started:

1. Download the Package
   Visit the project's GitHub page here and download the ZIP file containing the package.
2. Extract the Files
   After downloading, extract the ZIP file. You will find the package files at the following path: Copy\alexandria-plsql-utils-master\ora\ In this directory, locate the two files:
   • xlsx_builder_pkg.pks
   • xlsx_builder_pkg.pkb
3. Explore Additional Utilities
   The downloaded ZIP contains many other useful utility scripts that can simplify your PL/SQL development. I will provide more examples using these scripts in future posts.
4. Install the Package
   Install both xlsx_builder_pkg.pks and xlsx_builder_pkg.pkb into your Oracle database schema. You can do this by executing the scripts using SQL*Plus, SQL Developer, or any other Oracle client tool.

Creating a Directory Object for Excel Files

Before exporting data to Excel, you need to set up a database directory object where the generated Excel files will be stored. If you do not already have such a directory, create one as shown below:

Create OR Replace Directory excel_files as  'c:\excel_files';

Note:
Adjust the path according to your environment and operating system. For example, on Linux or Unix, the path might look like /home/oracle/excel_files.

Once the directory object is created, you are ready to export data from your Oracle tables into Excel files.

Exporting Data to Excel with PL/SQL

Below is an example demonstrating how to export all data from the emp table into a single-sheet Excel file using the xlsx_builder_pkg package:

BEGIN
xlsx_builder_Pkg.clear_workbook;
xlsx_builder_pkg.new_sheet ('emp');
xlsx_builder_pkg.query2sheet (p_sql => 'select * from emp', p_sheet => 1);
xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
END;

How This Example Works

• clear_workbook resets the current workbook to start fresh.
• new_sheet('emp') creates a new sheet named "emp" in the workbook.
• query2sheet executes the specified SQL query and populates the first sheet (p_sheet => 1) with data from the emp table.
• save('EXCEL_FILES', 'emp.xlsx') saves the workbook as emp.xlsx in the directory associated with the EXCEL_FILES directory object.

Tip:
The query2sheet procedure takes two parameters:

• p_sql is the SQL query to run
• p_sheet is the sheet number in the workbook
  In this example, since we are creating only one sheet, we use 1 as the sheet number.

Exporting Multiple Sheets to a Single Excel File

You can also export data from multiple tables into different sheets within the same Excel file. Here is an example:

BEGIN
xlsx_builder_Pkg.clear_workbook;
xlsx_builder_pkg.new_sheet ('emp');
xlsx_builder_pkg.query2sheet (p_sql => 'select * from emp', p_sheet => 1);
xlsx_builder_pkg.new_sheet ('dept');
xlsx_builder_pkg.query2sheet (p_sql => 'select deptno, dname from dept where deptno = 20',
 p_sheet => 2);
xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
END;

• The first sheet named "emp" is populated with all data from the emp table.
• The second sheet named "dept" is populated with data from the dept table where deptno is 20.
• Both sheets are saved into a single Excel file named emp.xlsx.

Conclusion

The xlsx_builder_pkg package is highly versatile and allows you to perform a variety of tasks related to Excel file generation and manipulation directly from PL/SQL. You are encouraged to explore the package documentation and experiment with its many features.

I will provide more practical examples and use cases for these utility scripts in my upcoming posts, so stay tuned for further tips and tricks to enhance your Oracle PL/SQL development!