Re: PL/SQL Loop and Spool Question

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

You can't really write a loop in SQLPlus, but you can spool out a new sql file...

Step 1. Create a script that executes the SQL you want executed for each person. For this example, call it CUST.SQL

Step 2. Before that statement executes, put the spool command before it, so the script looks like this:

spool &1
select <blah...blah> from whereever...
where...;

Step 3. Create ANOTHER script that does this:

spool my_file.sql
select '_at_cust '||to_char(cust_id)||';' from cust_list; spool off
_at_my_file

What this does create the sql file "MY_FILE.SQL" that contains the command to run your desired program for each customer, like this:

_at_cust 123;
_at_cust 124;
_at_cust 125;

When this is run, the first argument (ie 123, is known to sqlplus as &1), which it uses to construct the spool file name (it can be used in the query also)

Hope this gets you started on the right track.

Dan Hekimian-Williams Received on Sat Oct 16 1999 - 05:23:22 CEST