html - Oracle to Excel - PL/SQL export procedure

Question

I’m writing pl/sql procedure that exports data from Oracle to Excel. I need data formatting so I can’t use CSV. I’ve already tried with XML but it generates too large files when I want to export e.g. 70000 rows with 50 columns (almost 300 MB!!!).

That’s why I decided to use HTML tags to generate XLS file – it is smaller than XML and I must directly define format of only some special columns (strings, numbers and dates are formatted automatically by Excel). It’s very simple and convenient but I can’t define more than one worksheet.

Do you know how to add/define more than one worksheet in excel file written using HTML? I’ve tried to use VBScript formula like <% ActiveWorkbook.Worksheet.Add %>, but it doesn’t work.

Answer 1

Instead of creating Excel or HTML files on Oracle server, you can fetch Oracle data to existing Excel document via ODBC or OLEDB. The shortcoming is, that you should be careful with user permissions.

https://www.youtube.com/watch?v=Adz0zZFePf8