asked on

Oracle To Excel

Hi..
i want to export oracle data table to excel but the problem is when i do it from Oracle SQL Developer it hang .. i think because the rows is more than 100000 records.
how i can do it from sql Script
i think there is some way to output result into excel..
and how i divide it into many excel sheets

Thanks a lot

Ravindran Gopinathan

What function are you using? DBMS_OUTPUT or UTL_FILE? It is better to use UTL_FILE. If it is just a select query (rather than a PL/SQL block), then you can make use of SPOOL. You can spool the output to a XLS file directly.

What version of excel are u using? In older versions of excel, there is a limit on number of rows. Only 65k rows used to be allowed. I think there is no such limitation in latest versions

ASKER CERTIFIED SOLUTION

x77

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

what version of excel are you using? The latest one does not have the row limitation.

x77

I Tested with 2007 (12)

x77

The limitation on number of rows i by workseet, not WorkBook.
I think 64k Rows.

mrjoltcola

SQL Developer's limitation should not be based on Excel, or even having Excel installed. SQL Developer is a pure Java program.

You might try Toad instead. I have never had a problem exporting large rowcounts with Toad (commercial).

Devinder Singh Virdi

Here is the simplest way.
Set markup html on
spool newfile.XLS
select * from tab;
spool off
set markup html off

This will generate html file whose extention will be XLS and you can open in Excel.