This content has been marked as final. Show 8 replies
SQL developer freezes when huge set of data is extracted. As you say it is 15million records, use Toad if you have.
Spool the output in a txt file with columns Comma separated.
After spooling , Split the file into parts and save in XL sheets if you are not using latest MS-Excel.
Use OS commands to split large file. ( Much easier on Unix )
910874 wrote:Sorry, but this just sounds plain stupid.
The result set of one of my query contains around 15 million rows. Am trying to copy the resultset to an excel ....
EXCEL IS NOT A DATABASE!
Never mind the fact that Excel does not even support 1 million rows in a worksheet. It is not designed and not developed to deal with crunching and analysing millions of rows. A database is.
Use your software tools correctly. Treat Excel as a spreadsheet and the database as a database.
910874 wrote:No. You can connect your Excel directly to the database server via ODBC.
Is there a way to create a pivot table in spreadsheet use my the result set in SQL developer as a source?
Excel can thus pass a SQL query to Oracle. Oracle can do the major crunching of data, returning a manageable and relevant data set to Excel. Excel can then handle the rendering of that data set and provide additional analysis functionality on that data set.
To connect Excel to SQL-Developer, is client application to client application connectivity. This is not the norm. And requires both applications to support some kind of Inter Process Communication (IPC) interface to talk to one another. In Windows, a typical interface for this is (or was) DDE - Dynamic Data Exchange. This has evolved into more complex OLE, ActiveX and COM interfaces, but is still supported and a fairly easy thing to implement when you code an Windows application.
SQL-Developer is however a platform agnostic Java application that runs on multiple operating systems. And it is unlikely to provide extensive Windows style IPC integration using DDE/OLE/ActiveX/COM.
Here are the Excel limitations:
Worksheet size 1,048,576 rows by 16,384 columns
Worksheet size 65,536 rows by 256 columns
15 million records is far beyond that, not to mention a very bad use case of Excel.
Like others said, just use the database to do the crunching and Excel to just display the relevant data.
You might want to look into "Excel MS Query" (under Data -> From Other Sources -> Microsoft Query in Excel 2010). This will allow you to build a pivot table in Excel linked directly to your source database (via your ODBC connection). I'd expect performance with 15m records would be a problem but MSQuery lets you summarize and filter the data. Maybe you'd want get your result set down to < 100k rows and you could always create a couple different pivot views.