Oracle query output in excel

oracle plsql excel-2003
GrumP picture GrumP · Aug 3, 2012 · Viewed 68.2k times · Source

I have an Oracle 10G database and I need to write a fairly straightforward query that joins two tables and selects some data. However, I'd like to export the result list to an excel, so end users can use this .xls document to see the results and filter by one of the fields (location)

When I write the query, is there an easy way I can generate/ create an excel document that would hold these results as described above? The SQL doesn't need to run from within excel, but I guess that would be a useful feature now that I think about it! Thanks.

Answer

ora_excel picture ora_excel · Jan 15, 2014

There is simple solution for your request.

By using ora_excel, small pl/sql package which generates Excel xlsx file, you can select data and export selected data to Excel and set filtering.

Please see following example:

BEGIN    
    ORA_EXCEL.new_document;    

    ORA_EXCEL.add_sheet('My sheet');        
    ORA_EXCEL.query_to_sheet('select * from employees'); -- Select data from database   
    ORA_EXCEL.set_cells_filter('A1', 'K1'); -- Add cell filtering from column A1 to column K1    


    -- Save generated Excel to file with name example.xlsx to Oracle folder EXAMPLE_XLSX
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx'); 
END;

For more details please check here

Cheers

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More