oracle - How to export the result into different tabs of Excel in Toad for Data Analyst?

Question

Does anyone know how to export results from more than one query into different sheets of the same Excel workbook using the report automation in TOAD for data analyst? Thank you

Answer 1

I'm not sure that you can do that with Toad automatically but there is a little trick that you can do with Excel.

Write first query and execute it in Toad, after that right click on query result data grid and choose "Export dataset...", under Excel format choose "Excel instance" and click OK. It will open Excel and add one sheet with data from your query.

Repeat same process for second query and it will add another sheet to same document and fill with data from second query.

After you executed all queries and added it to Excel save excel document.

If you want to do that completely automatically, there is another solution which you can use to create single Excel document with multiple sheets which are loaded with data from different queries. Purchase the third party PL/SQL package, ORA_EXCEL.

Here is example how to do that:

BEGIN  
    ORA_EXCEL.new_document;  

    ORA_EXCEL.add_sheet('Employees');  
    ORA_EXCEL.query_to_sheet('select * from employees');  

    ORA_EXCEL.add_sheet('Departments');  
    ORA_EXCEL.query_to_sheet('select * from departments', FALSE);  

    ORA_EXCEL.add_sheet('Locations');  
    ORA_EXCEL.query_to_sheet('select * from locations');  

    -- EXPORT_DIR is an Oracle directory with at least  
    -- write permission  
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');  
END;

It can generate Excel file and store it to Oracle directory, or you can get generated Excel file into PL/SQL BLOB variable so you can store it to table or create your own process to distribute file like sending it to email.

More details you can find on products documentation/examples page: http://www.oraexcel.com/examples

Cheers