This content has been marked as final. Show 5 replies
You can also consider creating and HTML file which can be read easily with Excel.
The format is HTML but once you load in Excel you can easily save as the format you like.
SET SERVEROUTPUT ON SIZE UNLIMITED SET LIN 500 SET DEFINE OFF SET UNDERLINE OFF SET TAB OFF SET FEEDBACK OFF SET VERIFY OFF SET TERMOUT OFF SET PAGES 9999 SET MARKUP HTML ON TABLE "cellspacing=0 border=1" ENTMAP OFF spool myexcel.xls /* Replace the query below as you like */ SELECT * FROM EMP; spool off EXIT
Additional info in this post:Re: Saving SQL+ output to a text file
need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
My idea is to write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.
Oracle already wrote code that can export data to Excel (i.e. CSV) format. It's free code and is called sql developer.
You use a comment that is very similar to a hint but does not have the +, spaces and is case-sensitive.
SELECT /*csv*/ c.email_addr, c.job_title,. . . FROM myTable;
See this article by Jeff Smith, a frequent sql developer forum contributor, for other export options
SELECT /*csv*/ * FROM scott.emp;
SELECT /*xml*/ * FROM scott.emp;
SELECT /*html*/ * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/ * FROM scott.emp;
SELECT /*loader*/ * FROM scott.emp;
SELECT /*fixed*/ * FROM scott.emp;
SELECT /*text*/ * FROM scott.emp;
You need to execute your statement(s) as a script using F5 or the 2nd execution button on the worksheet toolbar. You’ll notice the hint name matches the available output types on the Export wizard.