0

Is there any way we can extract data from oracle tables to multiple sheets in an excel?

For example: I have two tables checkpoints and hold. I want data from checkpoints to go to sheet1 of MS excel and data from hold to go to sheet2.

Is this possible to implement in oracle?

Oracle version: Oracle 11G

  • Edit: I would like to implement this using PL/SQL. This code will be executed as a batch job.
Vivek
  • 4,452
  • 10
  • 27
  • 45
  • Maybe this can be of some help http://stackoverflow.com/a/477276/1083652 – A.B.Cade Feb 04 '13 at 16:00
  • possible duplicate of [Writing in ExcelSheet using UTL\_FILE package in Oracle](http://stackoverflow.com/questions/8414050/writing-in-excelsheet-using-utl-file-package-in-oracle) – Jon Heller Feb 04 '13 at 19:00

3 Answers3

4

If the spreadsheet you want to generate is very simple (i.e. just cells with data, and multiple sheets) you could try the Excel generation API in the Alexandria PL/SQL Library - just grab XLSX_BUILDER_PKG and its dependencies (I don't think there are many) and it should do the job.

I've used it myself to generate simple spreadsheets in XLSX format, and it's all in PL/SQL. It returns the results in a BLOB, and there's another routine in the library for spitting that out with UTL_FILE, if you need the result on the database server.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
0

Of course it is possible, but it depends how do you want that to be done.

1) If you have a reporting tool (Jasper Reports, Oracle BI) you can use that tool to create a report and export the data.

2) If you have a developer tool (Toad, SQL Developer) you can export the two tables "by hand".

3) If you know how to write code, than you can use a API to create the Excel file and populate it with the data. You can use PL/SQL, PHP, C, C++ or almost any other language, it just needs a Oracle API and an Excel API.

Alen Oblak
  • 3,285
  • 13
  • 27
-2

There is PL/SQL package that is able to create Excel document with multiple sheets and put data from SQL query to separate sheets.

Please see example below:

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;

More details you can find here: http://www.oraexcel.com/examples/pl-sql-excel-query-to-sheet-export

ora_excel
  • 112
  • 2