Re: PL/SQL Loop and Spool Question
Date: Mon, 04 Oct 1999 14:20:19 -0400
Message-ID: <37F8EFE3.68A62D87_at_Unforgettable.com>
If your OS is Unix, just wrap the whole thing in a unix shell script. Within that script, build a list of customer names and then loop through that list and extract the data and write it to the desired file.
Something like this:
#!/bin/ksh
Fname='customer.dat';
sqlplus -s scott/tiger
set pages 0;
set feedback off;
set termout off;
spool ${Fname};
whenever sqlerror exit failure;
select customer
from mytable;
exit success;
if [ $? -ne 0 ];then
print -u2 "Fatal error while building customer list";
exit 2;
fi
exec 3<"${Fname}";
if [ $? -ne 0 ];then
print -u2 "Unable to open ${Fname} for reading";
exit 2;
fi
while read -u3 Cust;do
Stmt=`select info from cust where cust_name=${Cust}";
sqlplus -s scott/tiger <<-sqlEOF
set pages 0;
set feedback off;
set termout off;
whenever sqlerror exit failure;
spool ${Cust}.dat
${Stmt};
exit success;
if [ $? -ne 0 ];then
print -u2 "Fatal error while extracting data for ${Cust}"; exit 2;
fi
done
exit 0;
Or something like that.
Ken
Rob Vicker wrote:
>
> I know that technically SQL*Plus doesn't support loops but, PL/SQL does. I
> am trying to read in customer values from the customer table and execute a
> SQL statement for each customer with using the customer name as a parameter
> to the query. This all seems to work fine but, I am also spooling this data
> to a file and I want to use the customer name as the name of the file (it is
> there billing detail). Does anyone know how to use a loop and set a new
> filename on the spool function each time you go through the loop?
>
> Thanks in advance.
>
> -Rob
Received on Mon Oct 04 1999 - 20:20:19 CEST