Single sql query to spool to multiple files


 
Thread Tools Search this Thread
Top Forums Programming Single sql query to spool to multiple files
# 1  
Old 12-16-2008
Single sql query to spool to multiple files

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  
Old 12-16-2008
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
Code:
spool somefile.dat
...... sql here
spool off
HOST split -l 1000 somefile.dat

# 3  
Old 12-16-2008
Quote:
Originally Posted by jim mcnamara
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
Code:
spool somefile.dat
...... sql here
spool off
HOST split -l 1000 somefile.dat

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  
Old 12-17-2008
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.

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

gives you the name of the filesystem. Or ask your dba.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Create Multiple UNIX Files for Multiple SQL Rows output

Dear All, I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File. The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files. Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies

2. UNIX for Dummies Questions & Answers

To pass multiple arguments from file in to an sql query

Hi all , I want to pass contents from a file say f1 as arguments to a sql query which has In statement using a script example select * from table_1 where login in ( `cat f1`) ; will this work or is there any other way to do it. (1 Reply)
Discussion started by: zozoo
1 Replies

3. Shell Programming and Scripting

SQL*PLUS Spool Output

Hi, Im writing a script to run a bit of sql(via sqlplus) that pulls back some data and spools it to a file, I want the spool file to only display the data, with no sql command at the top and no reports at the bottom ie(# of records recieved). I am currently doing it via a grep command but... (1 Reply)
Discussion started by: Magezy
1 Replies

4. Shell Programming and Scripting

UNIX/SQL loop to spool files!!

I want to spool files from unix using a sql script that would take values from another sql query within the KSH script.. unix loop select order_date from date_tbl for each order_Date i need to call this spool script with the value sqlplus scott/tiger@order_db @/ordspool/order_date.sql... (2 Replies)
Discussion started by: vr23
2 Replies

5. Shell Programming and Scripting

SQL query in a loop with single sqlplus connection

Hi, I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data. Is it possible to have a single sqlplus connection before entering the loop and... (4 Replies)
Discussion started by: lsantacana
4 Replies

6. UNIX for Dummies Questions & Answers

Grep multiple strings in multiple files using single command

Hi, I will use below command for grep single string ("osuser" is search string) ex: find . -type f | xarg grep -il osuser but i have one more string "v$session" here i want to grep in which file these two strings are present. any help is appreciated, Thanks in advance. Gagan (2 Replies)
Discussion started by: gagan4599
2 Replies

7. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

8. UNIX for Dummies Questions & Answers

split a single sql file into multiple files

Hi,I have a single sql file containing many create table ddl's.Example: CREATE TABLE sec_afs ( rpt_per_typ_c char(1) NOT NULL, rpt_per_typ_t varchar(20) NULL, LOCK ALLPAGES go EXEC sp_primarykey 'sec_afs', rpt_per_typ_c go GRANT SELECT ON sec_afs TO developer_read_only... (5 Replies)
Discussion started by: smarter_aries
5 Replies

9. Shell Programming and Scripting

Executing Multiple .SQL Files from Single Shell Script file

Hi, Please help me out. I have around 700 sql files to execute in a defined order, how can i do it from shell script (3 Replies)
Discussion started by: anushilrai
3 Replies

10. Shell Programming and Scripting

To spool output from a database query

Hi all, I would want to spool file for a database query, however by using crontab, the file is not spooled. Below shows my script: ORACLE_HOME="/u01/oraprod/perpdb/10.1.0/db_1" OUTFILE="/tmp/invalid.out" FILE="$HOME/admin/scripts" $ORACLE_HOME/bin/sqlplus -s "/as sysdba"... (0 Replies)
Discussion started by: *Jess*
0 Replies
Login or Register to Ask a Question

Featured Tech Videos