Single sql query to spool to multiple files

Applications Programming
1

Is there anyway to spool my select statement into spool files of max 10000 records each?

eg I have a select statement that will return 45000 records.
A normal spool command will output the 45000 into just one spool file.

How can I make sqlplus do this?
00001 - 10000 records --- spool file 1
10001 - 20000 records --- spool file 2
20001 - 30000 records --- spool file 3
30001 - 40000 records --- spool file 4
40001 - 45000 records --- spool file 5

The aim is to ensure that the large spool file does not go over the filesize limit..

2

You will have to write a PL/SQL code block that fetches rows, opening a new file and closing the old file every 1000 rows. Use the UTL_FILE file package.

A much simpler way to do this is to call the unix split command

spool somefile.dat
...... sql here
spool off
HOST split -l 1000 somefile.dat
3

Split seems good but what if the sql returned too many rows and the size of the spool file may go beyond the max file size ?

4

What OS are you on - does it not support "largefiles" -- files larger than what a 32 bit file pointer can reference offsets for? If this is the case you WILL have to resort to PL/SQL and UTL_FILE calls to open and close files. Note that most installations of Oracle control the "allowed" directories for utl_file to work against. So if you are creating a bunch of monster files, you script had better check free disk space on that filesystem first.

select value from v$parameter where name like '%utl_file_dir%';

gives you the name of the filesystem. Or ask your dba.