Toad World® Forums

Spool Large data into chunks

Hi

I’m a complete newbie at Oracle and Toad and I want to export/spool 1m+ rows from toad into flat file. However the query keeps aborting due to memory issues and only exports about 330k records.

the data which I am querying has data from 1990 to 2011 and I want to export all the years in separate files.

Is there a way I can create a loop statement which will export the data into chunks so 4 files of 330k records and by year?

the query I am running is like this:

set echo off
Set Trimspool onset

feedback off
set verify offset

termout off
set numwidth 20

set lines 10000

set pages 50000

set space 0

set pagesize 0

set linesize 50000

SET serveroutput ON;
SET verify OFF;

col todaydate new_value todaydate

SELECT TO_CHAR(SYSDATE,‘YYYYMMDD’) AS todaydate FROM dual;

SET termout OFF;

spool c:\dumpdata.txt

select *
from a
join b on x =x
join c on x=x
where a.a = 1
and a.b in (select a from ab)
and a.c in (select a from ac) ;

SPOOL OFF;

Thanks

After you have written a query and see the first 500 records, right click in the
results and use Export Dataset… Choose something like delimited text, be sure
to check off “Display all results in grid”. This gives you a very good chance of
success in generating the needed file with many records. Execute this query for
every year.

There are several options for creating flat files there. Depending on what you
want to do with the created files.

Groetjes,
Wim

On Fri, Jun 10, 2011 at 13:59, K Shah wrote:

Message from: Kruz

Hi

I'm a complete newbie at Oracle and Toad and I want to export/spool 1m+
rows from toad into flat file. However the query keeps aborting due to
memory issues and only exports about 330k records.

the data which I am querying has data from 1990 to 2011 and I want to export
all the years in separate files.

Is there a way I can create a loop statement which will export the data into
chunks so 4 files of 330k records and by year?

the query I am running is like this:

set echo off

Set Trimspool onset

feedback off

set verify offset

termout off

set numwidth 20

set lines 10000

set pages 50000

set space 0

set pagesize 0

set linesize 50000

SET serveroutput ON;

SET verify OFF;

col todaydate new_value todaydate

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD' ) AS todaydate FROM dual ;

SET termout OFF;

spool c:\dumpdata.txt

select *
from a
join b on x =x
join c on x=x
where a.a = 1
and a.b in (select a from ab)
and a.c in (select a from ac) ;

SPOOL OFF;

Thanks

_______________________________________

Historical Messages

Author: K Shah
Date: Fri Jun 10 04:59:58 PDT 2011
Hi

I'm a complete newbie at Oracle and Toad and I want to export/spool 1m+
rows from toad into flat file. However the query keeps aborting due to
memory issues and only exports about 330k records.

the data which I am querying has data from 1990 to 2011 and I want to export
all the years in separate files.

Is there a way I can create a loop statement which will export the data into
chunks so 4 files of 330k records and by year?

the query I am running is like this:

set echo off

Set Trimspool onset

feedback off

set verify offset

termout off

set numwidth 20

set lines 10000

set pages 50000

set space 0

set pagesize 0

set linesize 50000

SET serveroutput ON;

SET verify OFF;

col todaydate new_value todaydate

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD' ) AS todaydate FROM dual ;

SET termout OFF;

spool c:\dumpdata.txt

select *
from a
join b on x =x
join c on x=x
where a.a = 1
and a.b in (select a from ab)
and a.c in (select a from ac) ;

SPOOL OFF;

Thanks
__
_______________________________________

Hi

I would like to automate this as much as possible rather than having to change the year manually if its possible.

regards