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) ;
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;
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;