Re: PL/SQL Loop and Spool Question

From: Kenneth C Stahl <BlueSax_at_Unforgettable.com>
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

Original text of this message