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

Hi Mark,
  you need to use dbms_sql package to build your update string. Look in your code
snippet for example. I just wrote in line, and didn't test, but it gives you an example on how
to go about doing it. See docs for explanations of calls. My experience is with Oracle 8.0.4 doing
this.
 Hope that helps.
Cindy

Mark Wagner wrote:

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

    v_cursor          integer  default dbms_sql.open_cursor;
    v_s                  dbms_sql.VARCHAR2S;
    v_rows            integer default 0;



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



              begin

                v_s(1) := 'UPDATE deductees set (LASTNAME, FIRSTNAME,
CUSTOMERID) = ';
                v_s(2) := ' (select ispcustomer.LastName, ispcustomer.LastName,
ispcustomer.customerid ';
                v_s(3) := ' from ispcustomer, ispbilling, deductees ";
                v_s(4) := ' where deductees.employeeid = ' || numacct || ' AND
';
                v_s(5) := ' ispbilling.bankaccountnumber = deductees.employeeid
AND ispbilling.customerkey = ispcustomer.customerkey) ';
                dbms_sql.parse(v_cursor, v_s, v_s.first, v_s.last,  TRUE,
dbms_sql.NATIVE);
                v_rows := dbms_sql.execute(v_cursor);

             exception
                 when others then
                    dbms_output.put_line(sqlerrm);
                    raise;
             end;

> end loop;
> close test_cursor;

> dbms_sql.close(v_cursor);

>
> end;
> ..
> /
> ******************************************end pl/sql statement
> *********************************************
>
> Mark Wagner
> mark_at_cybertrails.com
Received on Wed Jul 14 1999 - 00:21:03 CDT