Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract data into file based on Month and Year

NSK2KSNOct 14 2015 — edited Oct 15 2015

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2015
Added on Oct 14 2015
11 comments
1,743 views