February 10th, 2004, 03:47 PM
output file on client, larger than 1000000
I am executing a query via a shell script that returns several hundred thousand rows of data. I wish to output the results to a tab delimited file on the client. As seen below, I have set the serveroutput size to 1000000 which I believe is the maximum.
if [ $# -lt 4 ]; then
echo "Usage: <schema> <user> <password> <outfile>"
rm $4 > /dev/null 1>&2
/u01/oracle/product/8.1.7/bin/sqlplus -s $2/$3@$1 << SQL
set echo off
set term off
set feedback off
set head off
set pagesize 0
set linesize 500
set serveroutput on size 1000000;
When i run the script it hangs. At first I thought my query was not joining the tables properly but when I executed the query by itself I worked fine (only took 2 minutes). Is there any way to get a lot of data to an output file on the client? I cannot use UTL_FILE b/c the person who will run this script will not have access to the server.
Thanks in advance for your help.