how can i spool the results to spreadsheet?

[maswien]

As the title, any one can help? Thanks.

 

[KenCunningham]

Take a look at thread759-873993 for details of spooling to a CSV file, which you can then convert to Excel. HTH.

 

[SantaMufasa]

Maswien,

Although you could genericise the (application-specific) script that appears in the thread that Ken mentions, following is the generic version of that script that you can run from any user that has access to the subject table, even if another user owns the table:

Section 1 -- Code to save to a script named "GenASCII.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept w prompt "Enter the Oracle username that owns the source table: "
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"'''
  from all_tab_columns
 where owner = upper('&w')
   and table_name = upper('&x');
prompt from &w..&x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Section 2 -- Sample invocation of "GenASCII.sql" (Note: you must run the code from a script [not just copy and paste] since the code contains an "ACCEPT...PROMPT..." sequence that works only from a script invocation.):

SQL> @GenASCII
Enter the Oracle username that owns the source table: test
Enter the table to ASCII: s_dept
Enter the flat file to write: dept.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool dept.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
||',"'||REGION_ID|| '"'
from test.s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "dept.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"
"99","Extra","1"

Output file = "dept.txt"

I believe this version will be preferrable to the other-thread version. Let us know how it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]