Home » SQL & PL/SQL » SQL & PL/SQL » Spool into excel file with different tabs (Oracle PLSQL)
Spool into excel file with different tabs [message #621486] Wed, 13 August 2014 13:23 Go to next message
m5124oracle
Messages: 4
Registered: August 2014
Junior Member
I have a requirement to produce an excel file through spool script and the output from two different sql queries should be spooled to two different tabs in the same excel file. I do not have privileges to use the dbms utl_file package. Please let me know how this can be achieved through a spool script.
Re: Spool into excel file with different tabs [message #621487 is a reply to message #621486] Wed, 13 August 2014 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WELCOME to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

SQL> connect scott/tiger
Connected.
SQL> set colsep ,
SQL> spool emp.csv
SQL> select * from emp;

     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM
----------,----------,---------,----------,---------,----------,----------
    DEPTNO
----------
      7369,SMITH     ,CLERK    ,      7902,17-DEC-80,       800,
        20

      7499,ALLEN     ,SALESMAN ,      7698,20-FEB-81,      1600,       300
        30

      7521,WARD      ,SALESMAN ,      7698,22-FEB-81,      1250,       500
        30


     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM
----------,----------,---------,----------,---------,----------,----------
    DEPTNO
----------
      7566,JONES     ,MANAGER  ,      7839,02-APR-81,      2975,
        20

      7654,MARTIN    ,SALESMAN ,      7698,28-SEP-81,      1250,      1400
        30

      7698,BLAKE     ,MANAGER  ,      7839,01-MAY-81,      2850,
        30


     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM
----------,----------,---------,----------,---------,----------,----------
    DEPTNO
----------
      7782,CLARK     ,MANAGER  ,      7839,09-JUN-81,      2450,
        10

      7788,SCOTT     ,ANALYST  ,      7566,19-APR-87,      3000,
        20

      7839,KING      ,PRESIDENT,          ,17-NOV-81,      5000,
        10


     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM
----------,----------,---------,----------,---------,----------,----------
    DEPTNO
----------
      7844,TURNER    ,SALESMAN ,      7698,08-SEP-81,      1500,         0
        30

      7876,ADAMS     ,CLERK    ,      7788,23-MAY-87,      1100,
        20

      7900,JAMES     ,CLERK    ,      7698,03-DEC-81,       950,
        30


     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM
----------,----------,---------,----------,---------,----------,----------
    DEPTNO
----------
      7902,FORD      ,ANALYST  ,      7566,03-DEC-81,      3000,
        20

      7934,MILLER    ,CLERK    ,      7782,23-JAN-82,      1300,
        10


14 rows selected.

SQL> spool off
SQL> 


Re: Spool into excel file with different tabs [message #621497 is a reply to message #621487] Wed, 13 August 2014 15:39 Go to previous messageGo to next message
m5124oracle
Messages: 4
Registered: August 2014
Junior Member
Thanks for your reply. However, I would need the output from two different queries to be shown in two different sheets in a single excel file.
Example - The output is produced in emp.xls
Sheet1 - output from select * from emp
sheet2 - output from select * from emp where ename = 'SMITH'
I am able to produce the output in excel but not in different sheets.
Re: Spool into excel file with different tabs [message #621498 is a reply to message #621497] Wed, 13 August 2014 15:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am able to produce the output in excel but not in different sheets.
This problem and solution have NOTHING to do with Oracle RDBMS & everything to do with MS Excel & how it wants data formatted.

Re: Spool into excel file with different tabs [message #621513 is a reply to message #621486] Wed, 13 August 2014 23:06 Go to previous messageGo to next message
m5124oracle
Messages: 4
Registered: August 2014
Junior Member
Thank you for the reply. I understand that its all how we format the excel file.
But just wanted to know if it is possible to spool the output of a query to a single excel file in different sheets through a spool script which should automatically get generated when the script is ran instead of manually formatting the excel.

Re: Spool into excel file with different tabs [message #621514 is a reply to message #621513] Wed, 13 August 2014 23:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Found these in google search :
http://stackoverflow.com/questions/6331446/spooling-of-data-into-different-sheets-in-a-excel
http://radio-weblogs.com/0137094/2006/10/26.html
http://www.oraexcel.com/
Re: Spool into excel file with different tabs [message #621593 is a reply to message #621514] Thu, 14 August 2014 13:38 Go to previous message
m5124oracle
Messages: 4
Registered: August 2014
Junior Member
Thanks for sharing the links. I was able to get the required output using the ExcelWorkbook Element and spooling the output of different queries into different sheets of a single excel file.
Previous Topic: Item Validation
Next Topic: How to eliminate duplicate rows from a table
Goto Forum:
  


Current Time: Fri Mar 29 07:32:56 CDT 2024