Export to Excel
One more neat solution from Michaels about exporting the data to Excel in this otn post
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
And it perfectly opens in Excel. No hassle with separator, no time lost in defining the column length, no bizarre xml format. Plain html, that is cool!
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 1981-11-17 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | 10 |
Simply great
May 11th, 2007 at 10:30
very neat indeed
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.
May 11th, 2007 at 12:17
I prefer
set pages 0 head on emb on newp noneMay 11th, 2007 at 16:38
set pages 0 head on emb on newp none
somehow does not produce headers for me
May 14th, 2007 at 08:03
Exactly!
Simply great!
Karl
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 …
May 16th, 2007 at 11:43
Laurent, this is great. I use this to produce html reports.
June 2nd, 2007 at 14:27
[…] XLS […]
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
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
July 16th, 2007 at 10:11
put the query in a file and use @file
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
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.
August 21st, 2007 at 20:05
I will have a look
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 < and > and not < and >
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.
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
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 ?
November 8th, 2007 at 04:17
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!!
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>
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
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 )
December 4th, 2007 at 21:35
Glad to help you
April 8th, 2008 at 15:13
Wow, this also give column headings in bold ! Great stuff!