Oracle export to excel

In this example, each 1000 records are transferred to one ExcelFile

Create Test Data

Code:
  
SQL> CREATE TABLE EXP_EXCEL (ID NUMBER, COL VARCHAR2(10));

Table created

SQL> INSERT INTO EXP_EXCEL SELECT LEVEL, 'COL_'||TO_CHAR(LEVEL,'FM000000') FROM DUAL CONNECT BY LEVEL<=3000;

3000 rows inserted

SQL> COMMIT;

Commit complete

---------------------------

  

Create a script named spool_xls. SQL
The script content is as follows:

Code:
  
set echo off
set linesize 1000 
set term off 
set verify off 
set feedback off 
set pagesize 50000
set heading off 
set trimspool on

spool exp_xls.sql
select 'SPOOL C:EXP_EXCEL_'||LEVEL||'.XLS'||CHR(10)||
       'SELECT * FROM (SELECT t.*, row_number() over(order by ID) as rn FROM exp_excel t) WHERE rn BETWEEN 1000 * ('||level||' -1) + 1 AND 1000 * '||level||';'||chr(10)||
       'SPOOL OFF'
  from dual
 connect by level <=(select ceil(count(*)/1000) from exp_excel);
spool off

set heading on
set markup html on entmap off spool on preformat off
@exp_xls.sql
set markup html off entmap off preformat on
set term on 
set verify on 
set feedback on 
set pagesize 14 
set linesize 80
set trimspool off
set echo off
prompt spool over .

----------------

  

Save the script. Execute the created script under SQL * Plus to generate the name exp _ in the root directory of drive C _Excel*. Xls file,ExcelOpen it.
(There should be 3 files)