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