Increase number of rows in SQL Developer export

Question

I'm running a SQL script in SQL Developer and wanting to export all the records it returns to a CSV file, roughly a few hundred thousand results.

Currently SQL Developer limits the number of rows it returns to 50.

I've tried the following to fix this:

• Increasing SQL array fetch size (I receive a protocol[0] error)
• Increasing max rows to print to script (made no difference)
• Increasing the max lines in script output (made no difference)

Code I'm using is as follows:

var UWY varchar2(20);
var SHOWS_AT_DATE_DD_MON_YY varchar2(30)

exec :UWY := '2012';
exec :SHOWS_AT_DATE_DD_MON_YY := '31-dec-2014';

SPOOL "c:\act-2012.csv";

    /* select statement */

SPOOL OFF;

Answer 1

Go to

Preferences-> Database-> Advanced -> increase the Sql Array Fetch Size

The max you can select here is 500, if you need more that you will have to export the results.

Answer 2

Go to Preferences-> Database->Worksheet and you can change the limits there.

Answer 3

I managed to resolve this issue by doing the following.

Running the script in SQL Developer (F5), then in the Query result window right clicking and pressing export and then following the instructions on screen.

Even though the query window only returned 50 results it exported all the results returned by the query.