Sponsored Content
Top Forums Programming Single sql query to spool to multiple files Post 302269061 by Leion on Tuesday 16th of December 2008 07:23:35 PM
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 ?
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
File::Queue(3pm)					User Contributed Perl Documentation					  File::Queue(3pm)

NAME
File::Queue - Persistent FIFO queue implemented in pure perl! SYNOPSIS
use strict; # always! use File::Queue; my $q = new File::Queue (File => '/var/spool/yourprog/queue'); $q->enq('some flat text1'); $q->enq('some flat text2'); $q->enq('some flat text3'); # Get up to first 10 elements my $contents = $q->peek(10); my $elem1 = $q->deq(); my $elem2 = $q->deq(); # empty the queue $q->reset(); DESCRIPTION
This module allows for the creation of persistent FIFO queue objects. File::Queue only handles scalars as queue elements. If you want to work with references, serialize them first! The module was written with speed in mind, and it is very fast, but it should be used with care. Please refer to the CAVEATS section. Interface File::Queue implements a OO interface. The object methods and parameters are described below. Methods File::Queue supports all of the queue-related functions a developer should expect. o new() Instantiates your File::Queue object. Parameters are described in the next sub-section. o enq() Enqueues a string element to the queue. o deq() Dequeues a string element from the queue, and returns the element. If the queue is empty, nothing is returned. o peek(n) Returns an arrayref containing the next n elements in the queue. If the queue size is less than n, all elements are returned. If the queue is empty, an empty arrayref is returned. o reset() Emptys the queue. o close() Closes the filehandles belonging to the queue object ('.dat' and '.idx'). o delete() Deletes the files belonging to the queue object ('.dat' and '.idx'). Parameters There are a number of parameters that can be passed when constructing your File::Queue objects. Parameters are case-insensitive. o File (required) File::Queue creates two files using this parameter as the base. In the case of the example in the SYNOPSIS, the two files are '/var/spool/yourprog/queue.dat' and '/var/spool/yourprog.idx'. The '.dat' file holds all of the data, the '.idx' file holds the byte index (pointer) of the starting point of the first element in the queue. o Mode (optional) The file bit mode to be shared by both the '.dat' and '.idx' files. Defaults to '0600'. o Seperator (optional) The character or byte sequence that is used to seperate queue elements in the '.dat' file. It should be something you can guarantee will NEVER appear in your queue data. Defaults to the newline character. o BlockSize (optional) This is the size of the byte chunks that are pulled at each iteration when checking for the end of a queued element. Defaults to 64, which will be fine for most cases, but can be tweaked or tuned for your specific case to squeeze out a few extra nanoseconds. CAVEATS
This module should never be used in situations where the queue is not expected to become empty. The '.dat' file is not truncated (emptied) until the queue is empty. Even the data you've already dequeued remains in the '.dat' file until the queue is empty. If you keep enqueueing elements and never FULLY dequeue everything, eventually your disk will fill up! SEE ALSO
Tie::File AUTHOR
Jason Lavold <jlavold [ at ] gmail.com> perl v5.10.0 2008-12-22 File::Queue(3pm)
All times are GMT -4. The time now is 07:30 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy