Suppose I have a some selected table result. I want to export it to as a excel file. It can do easily with SQLdeveloper. But I want to know is there anyway to do it in query level.
suppose my table is MYTABLE
then selected result set is
select * from MYTABLE
I want to genarate a excel sheet from above select statment.
You can't get a XLS(X) file, but CSV (comma-separated-values) will be good enough for Excel to open it. For example:
SQL> show user
USER is "SCOTT"
SQL> set colsep ';'
SQL> set pagesize 0
SQL> set linesize 100
SQL> spool emp.csv
SQL> select * from emp;
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;1982-12-09; 3000; ; 20
7839;KING ;PRESIDENT; ;1981-11-17; 10000; ; 10
7844;TURNER ;SALESMAN ; 7698;1981-09-08; 1500; 0; 30
7876;ADAMS ;CLERK ; 7788;1983-01-12; 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
14 rows selected.
SQL> spool off;
SQL> $emp.csv
"Running" (or "executing") EMP.CSV from the command prompt will, actually, start Excel and open the file.
SET set can/should be modified so that the final output looks prettier; the one I posted is really a quick & dirty way to do that.
