help ... am i doing this right? trying to create dynamic sql statement in pl/sql

From: Mark Wagner <mwagner_at_speedchoice.com>
Date: Tue, 13 Jul 1999 16:10:51 -0700
Message-ID: <931907683.253.5@news.remarQ.com>

Thankyou in advance for any help.
ok here is what im trying to do... im using the cursor to walk my tables

then im trying to update the deductees table with the information from the select query inside the loop using the variable numacct.

can you do that? can you use a pl/sql variable in an select statement for an update?
if so do you have to mark the variable somehow? like puting a & infront of a prompt variable??

****************************************pl/sql statement
**********************************************************

declare

namel ispCustomer.LastName%TYPE;
namef ispCustomer.LastName%TYPE;
idcustomer ispCustomer.LastName%TYPE;
numacct ispbilling.bankaccountnumber%TYPE;

cursor test_cursor is

        select  ispbilling.bankaccountnumber
        from ispbilling, deductees, ispcustomer
        where ispbilling.customerkey = ispcustomer.customerkey and
                ispbilling.bankaccountnumber = deductees.employeeid

begin

    open test_cursor;
    loop

          fetch test_cursor into numacct;
          exit when test_cursor%NOTFOUND;
          update deductees
              set (LASTNAME, FIRSTNAME, CUSTOMERID) =
                        (select ispcustomer.LastName, ispCustomer.LastName,
ispCustomer.customerid
                         from ispcustomer, ispbilling, deductees
                         where    deductees.employeeid = numacct  AND
                                        ispbilling.bankaccountnumber =
deductees.employeeid AND
                                        ispbilling.customerkey =

ispcustomer.customerkey);
end loop;
close test_cursor;
end;
..
/

******************************************end pl/sql statement
*********************************************

Mark Wagner
mark_at_cybertrails.com Received on Tue Jul 13 1999 - 18:10:51 CDT