I have got a Select which will give 100000 rows and written a spool command with Set parameters in a environment. Example below:
SET LINESIZE 10000
SET PAGE OFF
SPOOL EXPORT_DATA.DAT
SELECT COL1||'|'||COL2||to_date(arrivdate,'DD-MON-RRRR') FROM TABLENAME;
SPOOL OFF;
This is extracting data as required.
But now my requirement is changed to Assume I have data for below pattern
AUG2015 -> 2000 Records
SEP2015 -> 23433 Records
..
..
..
MAR2016 -> 2344 Records.
I want the data to be separated in to number of distinct MONTH and YEAR files. and generate that many files with file name also
EXPORT_DATA_AUG2015.DAT
EXPORT_DATA_SEP2015.DAT
..
..
..
EXPORT_DATA_MAR2016.DAT
I know we can achieve this using UTL_FILE requirement by using dynamic file name. but am worried about performance if we go on keep line by line into the file.
Is there anyway that we can dump the data in bulk into the file using PLSQL.
May thanks in advance.