Generate the query result in excel

Question

Currently i am using oracle report to generate the weekly reports in excel. There is sql queries behind this reports. The oracle report server is most of the time down and which i dont find reliable to generate weekly report.

So i want to automate my sql queries and generate report on excel instead of copying the query result and paste into excel. I can use Procedure or PL/SQL block to do this. But i am not sure whether its possible to create excel file and generate the report using PL/SQL.The PL/SQL block or procedure should be parameterized based on RESOURCE_ID coulmn as i can use the same procedure for another resources also. I am using oracle sql developer tool for writing the oracle queries.

--Query 1  
select  db,db_date,count(distinct sales_id)  
from Sales_Order  
where  
db='Test'  
and resource_id=2 and  
db_date between 20170710 and 20170716  
group by db,db_date  

--Query 2  
select  db,db_date,count(distinct it_id)  
from IT_INFO  
where  
db='Test'  
and resource_id=2 and  
db_date between 20170710 and 20170716  
group by db,db_date

And i want to generate the report on excel file like below:

Have a look at this answer - https://stackoverflow.com/questions/41299024/create-an-excel-file-xlsx-using-pl-sql — Sudipta Mondal, Jul 27 '17 at 09:23
Maybe define this query in Excel and refresh the data when Excel sheet is opened. — Wernfried Domscheit, Jul 27 '17 at 09:24
@SudiptaMondal i have seen that questions but it looks incomplete because it doesn't have step that we need to create a directory and give permission.. — Andrew, Jul 27 '17 at 09:30
@Andrew `select * from dba_directory` this table should have your directory details — Sudipta Mondal, Jul 27 '17 at 09:32
But how do i run queries in Excel ? I mean how do i get query result in excel ? — Andrew, Jul 27 '17 at 09:57

score 0 · Answer 1 · edited Jul 27 '17 at 10:47

Not sure what you want to do but definitely first thing you can do is to is to join the 2 queries:

Union All solution:

select  db,db_date,count(distinct sales_id) "SALES", "IT"
from Sales_Order  
where  
db='Test'  
and resource_id=2 and  
db_date between To_date('20170710','YYYYMMDD') and To_date('20170716','YYYYMMDD')
group by db,db_date  

UNION ALL

select  db,db_date, "SALES", count(distinct it_id)  "IT"
from IT_INFO  

where  
db='Test'  
and resource_id=2 and  
db_date between To_date('20170710','YYYYMMDD') and To_date('20170716','YYYYMMDD')
group by db,db_date

You can join the 2 tables using resource_id column:

select  so.db,so.db_dat,count(distinct so.sales_id) "SALES", count(distinct t_i.it_id) "IT"
from Sales_Order so
left outer join IT_INFO it_i on so.resource_id = it_i.resource_id --if this is corresponding column
where  
so.db='Test'  
and so.resource_id=2 and  
so.db_date between To_date('20170710','YYYYMMDD') and To_date('20170716','YYYYMMDD')
group by so.db,so.db_date

There is a possibility to export data directly to excel from Oracle SQL Developer:

Click Right mouse button > export > Next > Finish

this is not what the answer i am looking. As i said i can copy the result into excel but i need to automate it. And yes i can use Union condition to join the tables — Andrew, Jul 27 '17 at 11:50

Generate the query result in excel

1 Answers1