in

Export Data into Excel from Oracle Table Using PL SQL

An example is given below to export data into Excel from Oracle table using PL SQL. You can do this task with the help of xlsx_builder_pkg package, which you can download from GITHUB using the following link click here. After downloading the package extract the zip file and locate the package at this path \alexandria-plsql-utils-master\ora\. You will find the two files xlsx_builder_pkg.pks and xlsx_builder_pkg.pkb.

There are many other utility scripts also in the downloaded zip file, which you can use for your development. I will give more examples of those scripts in my other posts.

Install these two scripts into your database schema and create a directory object for Excel xlsx files (if you don't have already) as shown in below example:

Create Oracle Directory Object

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

Your database directory is now created and now you can create Excel file from Oracle table using PL SQL. Below is the example:

Export Data into Excel from Oracle Table using PL SQL

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;

Above example will export all the data from emp table to Excel file with one sheet named emp. Note, procedure query2sheet having two parameters one p_sql for select statement and the second p_sheet is the sheet number. As in the example, we are creating just one sheet with name emp, so we will pass 1 for p_sheet parameter. You can create multiple sheets with data. See the below 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;

export data into excel from Oracle table using pl sql

This will export the data from emp table into emp sheet and from dept table into dept sheet in one Excel file named emp.xlsx. You can study this package more and can perform more tasks. I will also give more examples from it.

Written by Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.org and orclqa.com a question and answer forum for developers. You can connect with me on Facebook and Twitter.

26 Comments

Leave a Reply
  1. Hi,

    i use this package xlsx_builder_pkg, it is supper, but i tried to change color, but it doesn't work:
    

    xlsx_builder_capi.cell (1, 6, 'Name', p_fontId => xlsx_builder_capi.get_font ('calibri', 2, 10, p_bold => true, p_rgb => 'Green'));

    • It is maybe because “Green” is not the RGB format.

      Try to specify color in RGB format, for example, ‘123, 111, 123’, or in any valid format. You can Google it to find right code for color green.

    • Yes I saw some examples on GitHub and found that they are specifying using Hex8 format. For example,

      xlsx_builder_pkg.cell( 1, 4, sysdate, p_fontId => xlsx_builder_pkg.get_font( 'Calibri', p_rgb => 'FFFF0000' ) );
      
  2. Hi,
    Processing consumes a lot of memory (UGA), I want
    Write the sheet content to a file each time you have completed the processing of 200 rows (bulk select). Write in append mode. therefore synchronize with reading data.
    Is it possible?

  3. Hello Vinish, I will use this package and attach .xls file through email, but the file is corrupted in attachment. how this issue is resolve, guide me?

    • After generating the Xls file, if you can open it but get corrupt in the attachment, you should check your email code.

    • I will use this process apex_mail.Add_Attachment. Tell me, how i will send in email this generated file. Any idea?

  4. Error report -
    ORA-06550: line 3, column 2:
    PLS-00221: 'NEW_SHEET' is not a procedure or is undefined
    ORA-06550: line 3, column 2:
    PL/SQL: Statement ignored
    ORA-06550: line 4, column 1:
    PLS-00221: 'QUERY2SHEET' is not a procedure or is undefined
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 5, column 18:
    PLS-00302: component 'SAVE' must be declared
    ORA-06550: line 5, column 1:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause:  Usually a PL/SQL compilation error.
    *Action:
    GETTING THIS ERROR PLEASE PROVIDE EXECUTION

Leave a Reply

Your email address will not be published. Required fields are marked *