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.
Below are the 2 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:
