Export report to Excel

Question

I want to export a table to an Excel file. I need to export a report.

ORA_EXCEL.new_document;
ORA_EXCEL.add_sheet('Sheet name');
ORA_EXCEL.query_to_sheet('select * from mytable');
ORA_EXCEL.save_to_blob(myblob);

I saved my table to blob.

How do I export/respond to the user (client)?

I need something that is simple to allow a user to be able to download an Excel file to their own computer. I tried doing this procedure in an Oracle workflow:

ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');

But this did not help, because it is saves the file to a directory on the server and I need it in the real server.

you cannot save the document to your client because database server have no idea about your client. you can share the oracle directory with client,run the script and then copy result to client; or need write some logic (function, webservice, etc...) that will prepare the xls file and then pass it to client — are, Nov 18 '15 at 10:30
Perhaps your app could send the spreadsheet to the user in an email. — Bob Jarvis - Слава Україні, Nov 18 '15 at 19:39

score 0 · Answer 1 · answered Nov 19 '15 at 03:07

The way I have handled similar issues in the past was to work with the systems people to mount a directory from either a web server or file server on the Database server.

Then create a directory object so that the procedure can save to a location that is accessible to the user.

If the files are not sensitive and there are a limited number of users then a file server makes sense as it is then just a matter of giving the user access to the file share.

If files are sensitive or this is a large number or unknown users we then used the Web server and sent a email with a link to the user enabling them to download their file. Naturally there needs to be security built into this to stop people being able to download other users files.

We didn't just email the files as an attachment because...

1) Emails with attachments tend to get blocked

2) We always advise not to open attachments on emails. (Yes I know we advise not to click on links as well but nothing is perfect)

i added this blob to table, then from table i gave link to user =)) — Altynbek, Nov 20 '15 at 11:32

score 0 · Answer 2 · edited May 23 '17 at 10:33

Who or what is invoking the production of the document?

If it´s done by an application, which the user is working on, this application can fetch the BLOB, stores it at f.e. TEMP-Directory and calls System.Diagnostics.Process.Start("..."); to open it with the associated application. (see Open file with associated application)

If it´s a website, this one could stream the blob back as Excel-Mimetype (see Setting mime type for excel document)

Also you could store in an Oracle-DIRECTORY, but this one has to be on the server and should be a netword-share to be accessible for clients (which is rarely accepted in a productive environment!)

If MAIL isn´t the solution, then maybe FTP can be a way to store files in a common share. See UTL_TCP - Package, with this a FTP-transfer can be achieved (a bit hard to code, but there are solutions to find in the web) and I guess, professional tools that generate Office-documents out of Oracle-DB and distribute them do it like this.

Export report to Excel

2 Answers2