• Post author:
  • Post category:PLSQL
  • Reading time:3 mins read

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.

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has 26 Comments

  1. Mounir

    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'));

    1. Vinish Kapoor

      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.

    2. Mounir

      in the Package we have:

      p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value

    3. Vinish Kapoor

      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' ) );
      
    4. Mounir

      Hi Vinish,
      it work, thank you, i like your job,

  2. Mike

    After creating the excel file in a Oracle Directory on the server, how can I download that file through Oracle APEX?

    1. Mike

      Thank you, I'll give that a try.
      Is there a way to accomplish this without an Oracle Directory?

    2. Vinish Kapoor

      To write a file on server, you need Oracle directory.

      And if you are storing the BLOB data into a table then you can get in Oracle Apex, there is no need of Oracle Directory for it.

    3. Mike

      Great, that's exactly what I need, how would I use xlsx_builder_pkg to create the excel file as BLOB data into a table?
      Thank you again for your help.

    4. Vinish Kapoor

      Declare a variable for blob, for example:

      Declare
         b_blob blob;
      

      After the following line:

      xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
      

      Add the below line:

      b_blob := xlsx_builder_pkg.finish;
      

      Now insert this b_blob variable into a table having blob column, for example:

      insert into my_blob_table (blob_column) values (b_blob);
      Commit;
      
    5. Mike

      That easy? You're awesome!
      Thank you again!

    6. Mike

      I'm getting this error: ora_sqlerrm: ORA-29280: invalid directory path ORA-06512.
      I guess is this line that causing the error, since I didn't create an Oracle Directory.
      xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
      Is there a way to get this working without an Oracle Directory?

    7. Vinish Kapoor

      You can remove this line, then it will not save the file on the server.

      Use only xlsx_builder_pkg.finish to get the file in blob.

    8. Mike

      That works, thank you!

    9. snkhalid

      Thanks for sharing pkg. Any idea how to start generate excel from row 15. and add some text before the table data display.

  3. Mounir

    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?

    1. Vinish Kapoor

      I didn't try this yet. If you have some ideas, you can try it and let me know if it works.

    2. Devi

      Hi Vinish

      I tried the above package and procedure . The stored procedure is looping
      Can you please help on this

  4. Ahsan

    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?

    1. Vinish Kapoor

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

    2. Ahsan

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

  5. Fama

    Hello,

    I can't find this procedure?
    
  6. alda

    Hello Vinish, i am using this proccedure, the xlsx file is formed, but can't be opened, is there any solution

  7. Selvendhran

    Hi Vinish,

    How to insert image in xlsx file.

  8. NAGA

    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

Comments are closed.