This content has been marked as final. Show 9 replies
Using PL/SQL you have a few approaches.
Would be one option to create a CSV file. If you need an Excel file, then you're going to have to look into something like SYLK
As Tubby said above, a CSV file is one way (and likely the simplest way).
Just in case, there is no "magic" that will take the output of:
and turn it into anything that is readable in Excel. You will need to do a TINY amount of work, which is, you will have to name each column in your select and separate them with commas yourself. Something like the following:
select * from <sometable>;
if you execute a statement like the one above in SQL*Plus with the proper options (so you don't get column headings, separating lines and the like) and spool it into a file you will end up with something that Excel will be very happy with.
select column1, ',' column2, ',' column3 from whatever_table;
Here is a complete example of what you have to do, study it, it is really easy to understand:
Credit where it is due: the code above is from "Oracle SQL*Plus: The Definitive Guide by Johnathan Gennick"
-- --This script extracts data from the employee --table and writes it to a text file in --a comma-delimited format. -- --Set the linesize large enough to accommodate the longest possible line. SET LINESIZE 80 --Turn off all page headings, column headings, etc. SET PAGESIZE 0 --Turn off feedback SET FEEDBACK OFF --Eliminate trailing blanks at the end of a line. SET TRIMSPOOL ON SET TERMOUT OFF SPOOL current_employees.csv SELECT '"ID","Billing Rate","Hire Date","Name"' FROM dual; SELECT TO_CHAR(employee_id) || ',' || TO_CHAR(employee_billing_rate) || ',' || TO_CHAR(employee_hire_date,'MM/DD/YYYY') || ',' || '"' || employee_name || '"' FROM employee WHERE employee_termination_date IS NULL; SPOOL OFF
Edited by: 440bx - 11gR2 on Aug 1, 2010 7:27 PM - Added due Credit
In addition to that, it's easier to use the COLSEP command than to manually append them in yourself :)
TUBBY_TUBBZ?set colsep , TUBBY_TUBBZ?select * from scott.emp; EMPNO,ENAME ,JOB , MGR,HIREDATE , SAL, COMM, DEPTNO ----------,----------,---------,----------,--------------------,----------,----------,---------- 7369,SMITH ,CLERK , 7902,17-DEC-1980 12 00:00, 800, , 20 7499,ALLEN ,SALESMAN , 7698,20-FEB-1981 12 00:00, 1600, 300, 30 7521,WARD ,SALESMAN , 7698,22-FEB-1981 12 00:00, 1250, 500, 30 7566,JONES ,MANAGER , 7839,02-APR-1981 12 00:00, 2975, , 20 7654,MARTIN ,SALESMAN , 7698,28-SEP-1981 12 00:00, 1250, 1400, 30 7698,BLAKE ,MANAGER , 7839,01-MAY-1981 12 00:00, 2850, , 30 7782,CLARK ,MANAGER , 7839,09-JUN-1981 12 00:00, 2450, , 10 7788,SCOTT ,ANALYST , 7566,19-APR-1987 12 00:00, 3000, , 20 7839,KING ,PRESIDENT, ,17-NOV-1981 12 00:00, 5000, , 10 7844,TURNER ,SALESMAN , 7698,08-SEP-1981 12 00:00, 1500, 0, 30 7876,ADAMS ,CLERK , 7788,23-MAY-1987 12 00:00, 1100, , 20 7900,JAMES ,CLERK , 7698,03-DEC-1981 12 00:00, 950, , 30 7902,FORD ,ANALYST , 7566,03-DEC-1981 12 00:00, 3000, , 20 7934,MILLER ,CLERK , 7782,23-JAN-1982 12 00:00, 1300, , 10 14 rows selected. TUBBY_TUBBZ?
Nice "trick" !!
I hadn't thought about that. Thank you, Tubby :)
if you need a simple CSV you can use one of the solutions mentioned above.
If you need an "Excel" file that allows for formatting or multiple worksheets you can use one of the following:
https://xml-spreadsheet.samplecode.oracle.com/ (it accepts a query or a cursor as argument) or
Others can be found at Re: How to save a query result and export it to, say excell?
Thanks Tubby and everyone, who took time to help me
Edited by: 785761 on Aug 1, 2010 11:19 PM
Many times we work in environments where we are not the dba of the production databases and we cannot create packages or user defined types to do our job. for example, How can we create an xml (xls) file if only we have a SELECT ANY DICTIONARY privillege?. Of course, we can use many Oracle tools to do that, one of them is SQL Developer but if we need to execute many SQL Statements we have to export as XLS document each SQL output and this is very tiring. Becouse of that, I recomend the method used in this link:
Maybe is not the correct solution but it was very usefull for me.
Hope this help and not a spam. :P
Edited by: johnxjean on 06-Dec-2010 19:15
dont worry so much... Use something like the below:
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;
SELECT * FROM EMP;
welcome to the forum. Is there a special reason why you try to reanimate an old, already answered thread?