Laurent Schneider

set feed off markup html on spool on 
alter session set nls_date_format='YYYY-MM-DD';
spool emp.xls
select * from emp;
spool off
set markup html off spool off

Put your code in <code> and </code> tags

25 Responses to “Export to Excel”

1. Sokrates Says:
   May 11th, 2007 at 10:30

   very neat indeed

2. Robbert Michel Says:
   May 11th, 2007 at 10:30

   You might want to add something like set pages 50000
   (unfortunately the max number)
   to that, unless you want the header to repeat every few lines.

3. Laurent Schneider Says:
   May 11th, 2007 at 12:17

   I prefer

   set pages 0 head on emb on newp none

4. Robbert Michel Says:
   May 11th, 2007 at 16:38

   set pages 0 head on emb on newp none
   somehow does not produce headers for me

5. Karl Reitschuster Says:
   May 14th, 2007 at 08:03

   Exactly!
   Simply great!

   Karl

6. Laurent Schneider Says:
   May 14th, 2007 at 09:58

   robbert, I am sorry about this, you are right, pages 0 is not working for HTML-Markup I hope I can find some alternative …

7. yas Says:
   May 16th, 2007 at 11:43

   Laurent, this is great. I use this to produce html reports.

8. Exportar datos a Excel, de una forma muy simple (Sql*Plus) « Oracle y yo…. Says:
   June 2nd, 2007 at 14:27

   […] XLS […]

9. ranjit Says:
   July 14th, 2007 at 00:04

   Hi Laurent,

   It is really amazing, I am able to get the out put but i am able to execute the entire sql code as a single script in plsql.

   Could you please help me out on this.

   Also after running the sql commands one by one, i am getting the select query which comes after spool, in the excel. I dont want this, please help us how can we avoid the query in output ecel file.

   the sql i am using…

   set feed off markup html on spool on
   set pages 0 head on emb on newp none
   alter session set nls_date_format=’YYYY-MM-DD’;
   spool emp.xls
   select * from emp;
   spool off
   set markup html off spool off

10. ranjit Says:
    July 14th, 2007 at 00:06

    Hi Laurent,

    It is really amazing, I am able to get the out put but i am not able to execute the entire sql code as a single script in plsql.

    Could you please help me out on this.

    Also after running the sql commands one by one, i am getting the select query which comes after spool, in the excel. I dont want this, please help us how can we avoid the query in output ecel file.

    the sql i am using…

    set feed off markup html on spool on
    set pages 0 head on emb on newp none
    alter session set nls_date_format=’YYYY-MM-DD’;
    spool emp.xls
    select * from emp;
    spool off
    set markup html off spool off

11. Laurent Schneider Says:
    July 16th, 2007 at 10:11

    put the query in a file and use @file

12. Peñaloza Marcelo Says:
    July 23rd, 2007 at 18:07

    Hi Laurent

    I live in Ecuador in South America. Work in Oracle 10g 5 years ago and I am very interest in learn more of Oracle. Work in Comercial company that buys and sells merchandise. I am Software developer in Oracle forms 10g.

    Tanks so much for your information. If you give me your web page it would be for my helpful.

    Excuses by the misspelling, my English is not very good although I try to improve it every day. In my country the official language is the Spanish.

    Many blessings

    mbpenaloza@yahoo.es

13. Kelly Says:
    August 20th, 2007 at 23:14

    Hi Laurent,

    Thanks for this great tip. I’m having one issue though - I have one column that is numeric and about halfway through the xls file, the values in this column are stored as text not numbers, which is very inconvenient. I know I can get the values of these cells and fix it manually, but is there a way that you know of to fix this in the spooling process? I do over 20 reports monthly that are then forwarded to a 3rd party who uses the data, which is why I’m not interested in scanning all the number columns to check for problems.

    Thanks.

14. Laurent Schneider Says:
    August 21st, 2007 at 20:05

    I will have a look

15. Laurent Schneider Says:
    August 23rd, 2007 at 14:37

    how did you figure out the values are stored as text? do you have a reproducible example?

    be careful with my blog, pasting code in comments is a pain, you need to use &lt; and &gt; and not < and >

16. salil Says:
    October 17th, 2007 at 11:13

    thanks,for providing wonderfull solution,
    i have a problem when the record is very large then it take lot of time, how to speed up this process.

17. Laurent Schneider Says:
    October 17th, 2007 at 21:32

    you can turn off screen output in your script. Only spooling to a file is usually faster than spooling to the screen (Terminal)

    set termout off

18. Anonymous Says:
    November 7th, 2007 at 18:15

    Excellent !

    For a bonus point !

    How can we add a company logo dynamically top the top of the file ?

19. Laurent Schneider Says:
    November 8th, 2007 at 04:17

    SQL> set mark html on table "><img src=/images/laurentschneider.png></table><br><table"
    SQL> select * from dept;
    

    

    DEPTNO	DNAME	LOC
    10	ACCOUNTING	NEW YORK
    20	RESEARCH	DALLAS
    30	SALES	CHICAGO
    40	OPERATIONS	BOSTON

20. Fernando Says:
    December 3rd, 2007 at 23:29

    Hi, thanks for that code.

    but, i have a question from someone without knowledge.
    if exists, it’s possible to put the result of that report on a procedure, then call it from other environment, as php or asp by example, and show the html in that window.. or put it the @file in other machine or export that string…
    i’m still looking on how do it… and i have no more ideas…

    thanks for all and sorry about my English… cause sucks!!

21. Laurent Schneider Says:
    December 4th, 2007 at 13:28

    Hi Fernando,
    You cannot mix PLSQL and SQLPLUS capabilities. You could however do it yourself

    
    begin
    :y:=’<table><tr><th align=right>EMPNO</th><th>ENAME</th></tr>’;
    for f in (select empno,ename from emp) loop
    :y:=:y||
    ‘<tr><td align=right>’||
    f.empno||
    ‘</td><td>’||
    f.ename||
    ‘</td></tr>’;
    end loop;
    :y:=:y||’</table>’;
    end;
    /
    <table>
    <tr><th align=right>EMPNO</th><th>ENAME</th></tr>
    <tr><td align=right>7369</td><td>SMITH</td></tr>
    <tr><td align=right>7499</td><td>ALLEN</td></tr>
    <tr><td align=right>7521</td><td>WARD</td></tr>
    <tr><td align=right>7566</td><td>JONES</td></tr>
    <tr><td align=right>7654</td><td>MARTIN</td></tr>
    <tr><td align=right>7698</td><td>BLAKE</td></tr>
    <tr><td align=right>7782</td><td>CLARK</td></tr>
    <tr><td align=right>7788</td><td>SCOTT</td></tr>
    <tr><td align=right>7839</td><td>KING</td></tr>
    <tr><td align=right>7844</td><td>TURNER</td></tr>
    <tr><td align=right>7876</td><td>ADAMS</td></tr>
    <tr><td align=right>7900</td><td>JAMES</td></tr>
    <tr><td align=right>7902</td><td>FORD</td></tr>
    <tr><td align=right>7934</td><td>MILLER</td></tr>
    </table>
    

22. Laurent Schneider Says:
    December 4th, 2007 at 13:40

    replace :y by your procedure out variable or whatever you like

    you could test it in sqlplus with

    VAR Y CLOB
    SET AUTOP ON

23. Fernando Says:
    December 4th, 2007 at 21:21

    Laurent, i have no words to describe, the helpfully this it!!
    thanks a lot!!

    (now, i can not erase the smile in my face )

24. Laurent Schneider Says:
    December 4th, 2007 at 21:35

    Glad to help you

25. Lucas Says:
    April 8th, 2008 at 15:13

    Wow, this also give column headings in bold ! Great stuff!

Leave a Reply