10 Replies Latest reply: Dec 12, 2010 10:57 AM by Etbin RSS

    aggregated data from oracle to excel report

    582588
      Hi all,

      I have a requirement where i need to fill in an excel report using aggregated data in oracle tables.

      example: i need to find total number of employees in each department and the total salaries of each department. My excel report looks like below

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      DEPT|total employees| total salaries
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      dept 10|15|75000
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      dept 20|0|0
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      dept 30|20|100000
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      dept 40|5|25000
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      TOTAL|40|200000
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


      declare
      begin
      execute immediate 'insert into abc select count(empid),sum(sal), dept_id from emp group by dept_id';
      end;

      the above query gives me the aggreagted output and then i need to manually insert the data into the excel report. Instead i need an oracle procedure that will automatically insert the data from abc table into the excel report.

      i know that the utl_file is used to export the data, but never used.
      any help will be really appreciated.

      Thanks
      S
        • 1. Re: aggregated data from oracle to excel report
          Etbin
          To start take a look at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056

          Regards

          Etbin
          • 2. Re: aggregated data from oracle to excel report
            582588
            i tried using spool as below:

            SPOOL C:\TEMP\TEST.XLS
            colsep ,
            SELECT sum(sal),job from emp;
            select * from emp
            pivot(count(emp_id)
            for deptno in (10,20,30,40) );
            SPOOL OFF;

            this will give the output in an excel sheet as
            job-sum(sal)
            -------------
            clerk-2500
            salesman-45000

            - deptno
            job - 10-20-30-40
            clerk-2000-0-230-140
            salesman-500 -400-300-0


            i want the output as below (the output of both the select statements should appear side by side )
            - deptno
            job - 10-20-30-40
            clerk-2000-0-230-140-2500
            salesman-500 -400-300-0-45000

            I have a summarized formatted excel and i need to fill the output of the above queries directly into the cells in the excel.

            Edited by: user579585 on Dec 12, 2010 12:17 AM
            • 3. Re: aggregated data from oracle to excel report
              Etbin
              To begin with your query should be something like below. NOT TESTED! I don't have database access.
              select *
                from (select job,
                             max(decode(dept,10,val)) val10,
                             max(decode(dept,20,val)) val20,
                             max(decode(dept,30,val)) val30,
                             max(decode(dept,40,val)) val40,
                             max(decode(dept,0,val)) val0,
                             1 kind
                        from (select dept,job,count(emp_id) val
                                from emp
                               group by dept,job
                              union all
                              select 0,job,sum(sal)
                                from emp
                               group by job
                             )
                      union all
                      select 'job',10,20,30,40,null,0
                        from dual
                     )
               order by kind,job
              submitting it to dump_csv will create the corresponding csv file on the server (provided you have everything required - directory, grants, OS access - in place)
              If you want to spool to the client (never done that) you'll have to construct single rows as strings
              select job || '|' || to_char(val10) || '|' || 
                                   to_char(val20) || '|' || 
                                   to_char(val30) || '|' || 
                                   to_char(val40) || '|' || 
                                   to_char(val0)
                from ( ...
              Regards

              Etbin
              • 4. Re: aggregated data from oracle to excel report
                790026
                Hi,

                Output Oracle report into HTML:
                sqlplus user/pass@inst
                
                SQL> set feed off markup html on spool on
                
                SQL> spool C:\MyReport.html
                <br>
                SQL&gt; select * from SomeTable;
                
                ...
                
                SQL&gt; spool off
                <br>
                SQL&gt; set markup html off spool off;
                <br>
                SQL>
                
                - Open obtained html report with browser; 
                Output Oracle report into EXCEL:
                sqlplus user/pass@inst
                
                SQL> set feed off markup html on spool on
                
                SQL&gt; spool C:\MyReport.xls
                <br>
                SQL&gt; select * from SomeTable;
                
                ...
                
                SQL&gt; spool off
                <br>
                SQL&gt; set markup html off spool off;
                <br>
                SQL>
                
                - Open obtained xls with Excel; 
                - In case of warning message "The file you're trying to open ... is in a different format ...
                  Do you want to open the file now? - press "Yes"
                
                {code}
                
                
                Good luck!
                
                --------------------------
                http://dba-star.blogspot.com/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: aggregated data from oracle to excel report
                  582588
                  thanks for all the help.

                  My problem is i have a predesigned excel sheet in report format and the output of the query has to fill say cells A15 to A17 in the excel sheet.

                  how can i assign the excel cells to the query output?

                  Any help will be appriciated.

                  Thanks
                  S
                  • 6. Re: aggregated data from oracle to excel report
                    Nelson Calero
                    Now the problem seems to be using excel.

                    One way to accomplish this is creating a dynamic area into an excel sheet, reading either a text file or running your query against the database, using the proper odbc driver in each case.

                    Regards.
                    • 7. Re: aggregated data from oracle to excel report
                      Etbin
                      just a thought:
                      Are queries made from excel acceptable from the security point of view ?
                      I mean: can it be done without revealing database username/password to the end user ?
                      Is the fact that the vast majority of end users isn't able to write queries good enough to justify such a practice ?

                      Regards

                      Etbin
                      • 8. Re: aggregated data from oracle to excel report
                        Nelson Calero
                        Security is another topic.

                        You can create a user with read access only to the tables needed by this query. This way, this user/password does not mean a compromise to database security.

                        End users does not need to write queries neither know SQL, because the new excel sheet with the dynamic data will be given to them to use, and the most they will need to do is refresh data inside the sheet, not to change the query.

                        Regards.
                        • 9. Re: aggregated data from oracle to excel report
                          Etbin
                          This way, this user/password does not mean a compromise to database security.
                          It might be that our selfmanagement era has distorted my perception of security but can you be sure that no one (now having access to emp table) seeing sum(sal) as in the query above won't be willing to invest some effort (google, ask around, ...) to arrive to select name,sal from emp (assuming sal being treated as restricted information)

                          Regards

                          Etbin
                          • 10. Re: aggregated data from oracle to excel report
                            Nelson Calero
                            mmm... if emp.sal is restricted information, then it won't be a good idea to give public access to it with a generic user.

                            Remember that you can create a view to hide data you don't want to expose.

                            Also, effort on securing access to data should be applied to the management of the reports you create.
                            How they will be distributed ?. Where will they be kept when printed ?. etc.

                            As I said before, security is another issue here. Database access is just one of them, but not the only one.

                            Regards.